How-To Use SQL Queries

Here are some tips to make efficient queries:

1. SELECT statement

The following syntax shows the structure of the SELECT statement and the FROM clause. The SELECT list is the list of fields, usually retrieved from tables. The FROM clause specifies one or more tables from which to retrieve data.

Example:

Retrieve all fields from the AUTHOR table using the * (star or asterisk) character. The * character tells the query to retrieve all fields in all tables in the FROM clause:

SELECT * FROM Author;

Specify an individual field by retrieving only the name of the author from the AUTHOR table:

SELECT Name FROM Author;

 

2. Filtering with the WHERE Clause

A filtered query uses the WHERE clause to include, or exclude, specific records. The WHERE clause is optional.

Example:

Retrieve the author whose primary key values is equal to 5:

SELECT * FROM Author WHERE Author_ID = 5;

Many relational databases use a special operator called BETWEEN, which retrieves between a range inclusive of the endpoints:

Example:

SELECT * FROM Author WHERE Author_ID BETWEEN 5 AND 10;

There are other ways of filtering (common to many relational databases), such as the LIKE operator. The LIKE operator is somewhat similar to a very simple string pattern matcher. The following query finds all authors with the vowel “a” in their names:

Example:

SELECT * FROM Author WHERE Name LIKE “%a%”;

You may want to search for a string that includes a percent sign or an underscore. In this case, you want SQL to interpret the percent sign as a percent sign and not as a wildcard character. You can conduct such a search by typing an escape character just prior to the character you want SQL to take literally. You can choose any character as the escape character, as long as that character doesn’t appear in the string that you’re testing, as shown in the following example:

Example:

SELECT Quote

FROM Bartletts

WHERE Quote LIKE ‘20#%’

ESCAPE ‘#’;

The % character is escaped by the preceding # sign, so the statement interprets this symbol as a percent sign rather than as a wildcard.

IN can be used as set membership operator:

Example:

SELECT * FROM Author WHERE Author_ID IN (1,2,3,4,5);

The NOT, AND, and OR operators are known as logical operators, or sometimes as logical conditions. This depends on the database in use. Logical operators allow for Boolean logic in WHERE clause filtering and various other SQL code commands and clauses. Mathematically, the sequence of precedence is NOT, followed by AND, and finally OR. Precedence can be altered using parentheses.

Example:

SELECT * FROM Author WHERE Author_ID = 5 AND Name LIKE “%a%”;

 

3. Precedence

Precedence is the order of resolution of an expression and generally acts from left to right, across an expression. In other words, in the following expression, each of the first, second, and third expressions are evaluated one after the other:

<expression1> AND <expression2> AND <expression3

An expression is a mathematical term representing any part of a larger mathematical expression. Thus, an expression is an expression in itself, can contain other expressions, and can be a subset part of other expressions. So in the expression ( ( ( 5 + 3 ) * 23 ) – 50 ), ( 5 + 3 ) is an expression, so is ( 5 + 3 ) * 23, so is ( ( 5 + 3 ) * 23 ), and even the number 50 is an expression, in this context.

Here is a table with precedence levels that can be applied to any RDBMS:

Level	Operator	Operation
1	*, /	multiplication, division
2	+, -, ||	addition, subtraction, concatenation
3	=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN, IN	comparison
4	NOT	exponentiation, logical negation
5	AND	conjunction
6	OR	disjunction

Remember that parenthesizing a part of an expression changes precedence, giving priority to the parenthesized section. Therefore, use of parentheses as in () has higher precedence than NOT, AND, and OR.

Example

The following query has precedence executed from left to right. It finds all Hardcover editions, of all books, regardless of the page count or list price. After Pages and Price are checked, the query also allows any hardcover edition. The OR operator simply overrides the effect of the filters against Pages and Price:

SELECT Print_Date, Pages, Price, Format FROM Edition

WHERE Pages < 300 AND Price < 50 OR Format = “Hardcover”;

The next query changes the precedence of the WHERE clause filter, from the previous query, preventing the OR operator from simply overriding what has already been selected by the filter on the Pages filter (now page counts are all under 300 pages):

SELECT Print_Date, Pages, Price, Format FROM Edition

WHERE Pages < 300 AND (Price < 50 OR Format = “Hardcover”);

 

4. Sorting Data

Sorting with the ORDER BY clause allows resorting into an order other than the natural physical order that records were originally added into a table. If you use the ORDER BY clause, it must be the last clause of the SQL statement. You can specify an expression, an alias, or a column position as the sort condition.

You can also specify the type of order:

ASC – Orders the rows in ascending order (This is the default order.)

DESC – Orders the rows in descending order

Example

This example sorts by Author_ID, contained within the name of the author (the Name field):

SELECT * FROM Author ORDER BY Name, Author_ID DESC;

 

5. GROUP BY Clause

An aggregated query uses the GROUP BY clause to summarize repeating groups of records into aggregations of those groups. Typical types of group functions: AVG, SUM, MAX, MIN, COUNT.

Please note the sequence of the different clauses. The WHERE clause is always executed first, and the ORDER BY clause is always executed last. It follows that the GROUP BY clause always appears after a WHERE clause, and always before an ORDER BY clause.

A simple application of the GROUP BY clause is to create a summary, as in the following example, creating an average price for all editions, printed by each publisher:

Example

SELECT p.Name AS Publisher, AVG(e.Price)

FROM Publisher p JOIN Edition e USING (Publisher_ID)

GROUP BY p.Name;

In this example, an average price is returned to each publisher. Individual editions of books are summarized into each average, for each publisher; therefore, individual editions of each book are not returned as separate records because they are summarized into the averages.

The next example selects only the averages for publishers, where that average is greater than 10:

Example

SELECT p.Name AS Publisher, AVG(e.Price)

FROM Publisher p JOIN Edition e USING (Publisher_ID)

GROUP BY p.Name;

HAVING AVG(e.Price) > 10;

 

6. HAVING Clause

Groups are formed and group functions are calculated before the HAVING clause is applied to the groups.

Please note. A common error is to get the purpose of the WHERE and HAVING clause filters mixed up. The WHERE clause filters records as they are read (as I/O activity takes place) from the database. The HAVING clause filters aggregated groups, after all, the database I/O activity has completed. Don’t use the HAVING clause when the WHERE clause should be used, and visa versa.

 

7. JOINS

A join query is a query retrieving records from more than one table. Records from different tables are usually joined on related key field values. The most efficient and effective forms of join are those between directly related primary and foreign key fields. There are a number of different types of joins:

Inner Join—an intersection between two tables using matching field values, returning records common to both tables only.

The following query is an inner join because it finds all publishers and related published editions. The two tables are linked based on the established primary key to the foreign key relationship. The primary key is in the Publisher table on the one side of the one-to-many relationship, between the Publisher and Edition tables. The foreign key is precisely where it should be, on the “many” side of the one-to-many relationship.

Example

SELECT p.Name AS Publisher, e.ISBN

FROM Publisher p JOIN Edition e on p.Publisher_ID=e.Publisher_ID;

Please note. Use aliases as substitutes for table/column names. Without the alias, the query would simply have table names, much longer strings, making the query a little more difficult to read and code. Alias is a word followed after AS or, alternatively, after space.

Cross join—this is also known mathematically as a Cartesian product. A cross joins merges all records in one table with all records in another table, regardless of any matching values. A cross-join simply joins two tables regardless of any relationship. The result is a query where each record in the first table is joined to each record in the second table (a little like a merge):

Example

SELECT p.Name AS Publisher, e.ISBN

FROM Publisher p CROSS JOIN Edition;

Outer join—returns records from two tables as with an inner join, including both the intersection between the two tables, plus records in one table that are not in the other. Any missing values are typically replaced with NULL values.

Outer joins can be of three forms:

Left outer join—all records from the left side table plus the intersection of the two tables. Values missing from the right side table are replaced with NULL values. This query finds the intersection between publishers and editions, plus all publishers currently with no titles in print:

Example

SELECT p.Name AS Publisher, e.ISBN

FROM Publisher p LEFT OUTER JOIN Edition e on p.Publisher_ID=e.Publisher_ID;

Right outer join—all records from the right side table plus the intersection of the two tables. Values missing from the left side table are replaced with NULL values. In this example, books without a publisher would have NULL valued publishing house entries:

Example

SELECT p.Name AS Publisher, e.ISBN

FROM Publisher p RIGHT OUTER JOIN Edition e on p.Publisher_ID=e.Publisher_ID;

Full outer join—the intersection plus all records from the right side table not in the left side table, in addition to all records from the left side table not in the right side table. This query finds the full outer join, effectively both the left and the right outer joins at the same time:

Example

SELECT p.Name AS Publisher, e.ISBN

FROM Publisher p FULL OUTER JOIN Edition e on p.Publisher_ID=e.Publisher_ID;

Self Join—a self-join simply joins a table to itself and is commonly used with a table containing a hierarchy of records (a denormalized one-to-many relationship). A self-join does not require any explicit syntax other than including the same table in the FROM clause twice, as in the following example:

Example

SELECT p.Name AS Parent_Name, s.Name

FROM Subject p JOIN Subject s ON (s.Parent_ID= p.Parent_ID);

Please note. When using Self Join it is mandatory to use two different aliases for one table.

 

8. ON versus WHERE

The function of the ON and WHERE clauses in the various types of joins are potentially confusing. These facts may help you keep things straight:

• The ON clause is part of the inner, left, right, and full joins. The cross join and UNION join don’t have an ON clause because neither of them does any filtering of the data.
• The ON clause in an inner join is logically equivalent to a WHERE clause; the same condition could be specified either in the ON clause or a WHERE clause.

Example

SELECT p.Name AS Publisher, e.ISBN

FROM Publisher p JOIN Edition e on p.Publisher_ID=e.Publisher_ID;

will show the same result as:

SELECT p.Name AS Publisher, e.ISBN

FROM Publisher p, Edition e

WHERE p.Publisher_ID=e.Publisher_ID;

• The ON clauses in outer joins (left, right, and full joins) are different from WHERE clauses. The WHERE clause simply filters the rows that are returned by the FROM clause. Rows that are rejected by the filter are not included in the result. The ON clause in an outer join first filters the rows of a cross product and then includes the rejected rows, extended with nulls.

 

9. Nested Queries

A nested query is a query containing other subqueries or queries contained within other queries. It is important to note that use of the term “nested” means that a query can be nested within a query, within a query, and so on—more or less ad infinitum, or as much as your patience and willingness to deal with complexity allows.

The following query finds all authors, where each author has a publication, each publication has an edition, and each edition has a publisher:

Example

SELECT * FROM Author WHERE Author_ID IN

(SELECT Author_ID FROM Publication WHERE Publication_ID IN

(SELECT Publication_ID FROM Edition WHERE Publisher_ID IN

(SELECT Publisher_ID FROM Publisher)));

 

Subqueries can produce single scalar values. In this query, the subquery passes the AUTHOR_ID value for the filtered author, back to the query on the PUBLICATION table—it passes a single AUTHOR_ID value (a single value is a scalar value):

Example

SELECT Author_ID, Title FROM Publication WHERE Author_ID =

(SELECT Author_ID FROM Author WHERE Name = ‘James Blish’);

Please note. When you use a subquery in an “=” comparison, the subquery’s SELECT list must specify a single column. Otherwise, an error will occur. You can guarantee that a subquery will return a single value if you include an aggregate function in it. Aggregate functions always return a single value.

Example

SELECT Model, ProdName, ListPrice

FROM Product

WHERE ListPrice =

(SELECT MAX(ListPrice) FROM Product);

In the preceding nested query, both the subquery and the enclosing statement operate on the same table. The subquery returns a single value: the maximum list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have that list price.

The next example shows a comparison subquery that uses a comparison operator other than =:

Example

SELECT Model, ProdName, ListPrice

FROM Product

WHERE ListPrice <

(SELECT AVG(ListPrice) FROM Product);

The subquery returns a single value: the average list price in the PRODUCT table. The outer query retrieves all rows from the PRODUCT table that have a lower list price than the average list price.

 

10. Composite Queries

Set merge operators can be used to combine two (or more) separate queries into a merged composite query. Both queries must have the same data types for each field, all in the same sequence. The term set merge implies a merge or sticking together of two separate sets of data. In the case of the following query, all records from two different tables are merged into a single set of records:

Example

SELECT CustomerID, FirstName, LastName

FROM Customer

UNION

SELECT EmployeeID, FirstName, LastName

FROM Employees

Remember:

• The tables must all have the same number of columns.
• Corresponding columns must all have identical data types and lengths.

UNION operation normally eliminates any duplicate rows that result from its operation, which is the desired result most of the time. Sometimes, however, you may want to preserve duplicate rows. On those occasions, use UNION ALL.

 

11. Using Reserved Words Correctly

Given the fact that SQL makes constant use of command words such as CREATE, ALTER, SELECT and WHERE it stands to reason that it would probably be unwise to use these same words as the names of tables or variables. To do so is a guaranteed way to confuse your DBMS. In addition to such command words, a number of other words also have a special meaning in SQL. These reserved words should also not be used for any purpose other than the one for which they are designed. SQL has hundreds of reserved words, and you must be careful not to inadvertently use any of them as the names of objects or variables.

If you want to use spaces in object or column names make sure to enclose them in square brackets (or double quotes – it depends on your RDBMS).

 

12. Handling Null Values

A null value is a nonvalue. If you are talking about numeric data, a null value is not the same as zero, which is a definite value. It is one less than one. If you are talking about character data, a null value is not the same as a blank space. A blank space is also a definite value. If you are talking about Boolean data, a null value is not the same as FALSE. A false Boolean value is a definite value too.

A null value is the absence of a value. A field may contain a null value for several reasons:

• A field may have a definite value, but the value is currently unknown.
• A field may not yet have a definite value, but it may gain one in the future.
• For some rows in a table, a particular field in that row may not be applicable.
• The old value of a field has been deleted, but it has not yet been replaced with a new value.

In any situation where knowledge is incomplete, null values are possible.

A condition that evaluates to UNKNOWN acts almost like FALSE. For example, a SELECT statement with a condition in the WHERE clause that evaluates to UNKNOWN returns no rows. However, a condition evaluating to UNKNOWN differs from FALSE in that further operations on an UNKNOWN condition evaluation will evaluate to UNKNOWN. Thus, NOT FALSE evaluates to TRUE, but NOT UNKNOWN evaluates to UNKNOWN.

Consider the following:

Condition	Value of a	Evaluation
a IS NULL	10	FALSE
a IS NOT NULL	10	TRUE
a IS NULL	NULL	TRUE
a IS NOT NULL	NULL	FALSE
a = NULL	10	UNKNOWN
a != NULL	10	UNKNOWN
a = NULL	NULL	UNKNOWN
a != NULL	NULL	UNKNOWN
a = 10	NULL	UNKNOWN
a != 10	NULL	UNKNOWN

 

13. CASE Expression

SQL has two different CASE structures: the CASE expression and the CASE statement.

The CASE expression, like all expressions, evaluates to a single value. You can use a CASE expression anywhere where a value is legal. The CASE statement, on the other hand, doesn’t evaluate to a value. Instead, it executes a block of statements.

The CASE expression searches a table, one row at a time, taking on the value of a specified result whenever one of a list of conditions is TRUE. If the first condition is not satisfied for a row, the second condition is tested, and if it is TRUE, the result specified for it is given to the expression, and so on until all conditions are processed. If no match is found, the expression takes on a NULL value. Processing then moves to the next row.

Example

SELECT CASE

WHEN Specialty = ‘Brakes’ THEN ‘Brake Fixer’

WHEN Specialty = ‘Engines’ THEN ‘Motor Master’

WHEN Specialty = ‘Electrical’ THEN ‘Wizard’

ELSE ‘Apprentice’

END;

 

The outcome of the statement containing the CASE expression depends on which of several specified values in the table field is equal to each table row.

Example

SELECT CASE Rank

WHEN ‘general’ THEN ‘Gen.’

WHEN ‘colonel’ THEN ‘Col.’

WHEN ‘lieutenant colonel’ THEN ‘Lt. Col.’

WHEN ‘major’ THEN ‘Maj.’

WHEN ‘captain’ THEN ‘Capt.’

WHEN ‘first lieutenant’ THEN ‘1st. Lt.’

WHEN ‘second lieutenant’ THEN ‘2nd. Lt.’

ELSE NULL

END

FROM Officers;