Searching SQL-92 Databases using SELECT, WHERE, and Query Conditions

Most database searches have the simple form (column) (relator) (literal). For example, to select all the rows in table TABLE_1 which have a column COLUMN_1 containing the value 5555, you'd say:
SELECT * FROM TABLE_1 WHERE COLUMN_1 = 5555;

Sometimes the search is for a combination of columns and so you'd use the usual AND|OR|NOT and parenthesization, as in:
SELECT * FROM TABLE_1 WHERE COLUMN_1 = 5555 OR
(COLUMN_1 = 5554 AND COLUMN_2 = 7);

Other relators besides '=' are what you'd expect if you've used any programming language: > and >= and < and <=. The only potential point of confusion is that the operator for 'not equals' is <> as in BASIC. There are many texts which will tell you that != is SQL's not-equals operator; those texts are false; it's one of those unstampoutable urban myths.

The following examples show some of the more advanced selection options.

SELECT * FROM TABLE_1 WHERE UPPER(COLUMN_2) = 'GOMEZ';
Here we're using an expression on the column, an operation which would be necessary if you wanted to find 'Gomez' as well as 'GOMEZ'. For a full description of the various operators and functions that you can use in expressions, see Functions and Operators .

SELECT COLUMN_1,'X',COLUMN_2,'Y' FROM TABLE_1 WHERE COLUMN_1 <> 0;
Here we're showing that you don't have to select everything using *. You can select columns, literals, or combinations of columns and literals.

SELECT COLUMN_1,(SELECT COLUMN_2 FROM TABLE_2) FROM TABLE1;
Here's a selection within a selection. It only works if there's a single row in TABLE_2.

SELECT COLUMN_1 FROM TABLE_1,TABLE_2
WHERE TABLE_1.COLUMN_1 = TABLE_2.COLUMN_1;
Here's what's called a "join". There are many kinds of joins -- equijoin, natural join, left/right/full outer join, (table expression) joins, n-way joins -- and THE OCELOT SQL DBMS knows them all.

SELECT COLUMN_1 FROM TABLE_1 EXCEPT SELECT COLUMN_2 FROM TABLE_2;
Here's a case where you should envisage those Venn diagrams that you studied way back some time or another. SQL results are "sets" so you can perform the usual "set" operators against them, namely UNION and EXCEPT and INTERSECT.

SELECT COLUMN_1 FROM TABLE_1 WHERE COLUMN_1 LIKE '%XXX%';
Here's another operator besides the usual = > < >= <= operators. The search is for any row containing XXX anywhere within COLUMN_1. Some other relational operators are: OVERLAPS, UNIQUE, MATCH, EXISTS, IN, BETWEEN.

SELECT DISTINCT COLUMN_1 FROM TABLE_1 ORDER BY COLUMN_1;
This selection lacks a WHERE clause, which means there is no filtering: take all rows. The results should come out sorted by the values in COLUMN_1 (according to the collating sequence of COLUMN_1 if COLUMN_1 has a CHAR datatype). If there are duplicate results they are eliminated by the DISTINCT operator.

SELECT COLUMN_1,COUNT(COLUMN_1) FROM TABLE_1
GROUP BY COLUMN_1 HAVING COUNT(COLUMN_1) > 1;
Here we're taking all the rows in TABLE_1 and grouping together the ones that have the same value in COLUMN_1. Then we filter out the groups which have more than 1 row in them (the COUNT function is a how-many scalar operator). Then we display the information about the groupings.

Return to Ocelot home page