Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Aug 4, 2005

Just SQL Part III - Where is it?

By James Koopmann

We do not always want to SELECT everything from a table. The matter of finding the information required is a function of implementing the optional WHERE clause of the SELECT statement.

In the previous Part of this series we looked at the 'simple SELECT' statement. This involved the four required pieces of a SQL statement. Namely the key word SELECT, a select list or columns we wanted to return from the table, the FROM key word to specify the table information was going to be retrieved from, and then the name of the table itself. The simple SELECT statement when running will return every row in a table. More often then not, we are only concerned with a subset of rows from a table. That is where this article picks up and explores the use of the optional WHERE clause.

Table 1.
DOG_ORIGIN table

Country

Breed

Breed_size

Germany

German Shepherd Dog

Big

Germany

Dobermann

Big

Germany

Rottweiler

Big

USA

Siberian Husky

Medium

USA

Alaskan Malamute

Medium

USA

American Bulldog

Big

Switzerland

Bernese Mountain Dog

Big

Switzerland

Saint Bernard Dog

Big

Switzerland

Entlebuch Cattle Dog

Medium

Australia

Australian Cattle Dog

Medium

Australia

Jack Russell Terrier

Small

The WHERE clause

The optional WHERE clause, simply stated, imposes search criteria on top of a SELECT statement giving the affect of conditionally selecting those rows from a table you are interested in displaying. Again using Table 1 from Part II of this series there was a simple select statement that listed the all countries of origin for dog breeds. The SQL looked like this.

SELECT country, breed FROM dog_origin;
COUNTRY                        BREED
------------------------------ -----------------------
Germany                        German Shepherd Dog
Germany                        Dobermann
Germany                        Rottweiler
USA                            Siberian Husky
USA                            Alaskan Malamute
USA                            American Bulldog
Switzerland                    Bernese Mountain Dog
Switzerland                    Saint Bernard Dog
Switzerland                    Entlebuch Cattle Dog
Australia                      Australian Cattle Dog
Australia                      Jack Russell Terrier

Now suppose we were interested in dog breeds only from Germany we would need to put a WHERE condition on the SELECT statement. The following WHERE clause produces a result set for all dog breeds from Germany. The "country = 'Germany'" part of the WHERE clause is considered the search condition that must be met for a row to be returned. The equal sign is called the predicate of the search conditional. Also, please note that the country 'Germany' must match exactly to the data stored in the DOG_ORIGIN table. A condition of 'WHERE country = 'GERMANY' would not work.

SELECT breed FROM dog_origin WHERE country = 'Germany';
BREED
-----------------------
German Shepherd Dog
Dobermann
Rottweiler

The AND conjunctive

Often times a single search condition is not enough and we would like to search on multiple conditions. The logical operator AND facilitates this purpose and is considered part of a WHERE clause. Just as you would suspect, when using the AND conjunctive, both conditions between the AND must be true for a result set to be returned. Therefore, if we wanted to look for medium sized dogs in the USA we would construct a query like the following:


SELECT breed 
  FROM dog_origin WHERE country = 'USA' AND breed_size = 'Medium';
BREED
-------------------
Siberian Husky
Alaskan Malamute

The OR conjunctive

Where the AND conjunctive is mandatory for both conditions and returning row, the logical operator OR specifies that one or the other conditions need only be true for a result set to be returned. This is just as you would suspect and if we wanted to display the dogs from countries that were big or small we could construct the following:


SELECT country, breed, breed_size 
  FROM dog_origin WHERE breed_size = 'Big' OR breed_size = 'Small';
COUNTRY                        BREED                          BREED_SIZE
------------------------------ ------------------------------ ------------
Germany                        German Shepherd Dog            Big
Germany                        Dobermann                      Big
Germany                        Rottweiler                     Big
USA                            American Bulldog               Big
Switzerland                    Bernese Mountain Dog           Big
Switzerland                    Saint Bernard Dog              Big
Australia                      Jack Russell Terrier           Small


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM