Many SQL tutorials and courses start with basic queries, that allow data to be displayed in convenient ways. The next step is to be able to do more advanced table queries and joins. This article explains
- How to display only one instance of each record type
- How to join tables when one table has no equivalent entry to the first
- How to combine two or more tables that have different structures
SQL DISTINCT Command
As part of a product recall, the user may wish to print a list of customer that have placed orders in the last month. Since some customers are likely to have placed several orders, a query of the recent order book will return the names of some customers several times. The SQL DISTINCT command is used to give the user what is needed. Referring to Figure 1:
SELECT DISTINCT CUSTNAME
FROM ORDERS
WHERE ORDERDATE > SYSDATE - 31
The SYSDATE - 31 line is useful as it does not require the SQL query to be updated each week, month etc.
SQL LEFT OUTER and RIGHT OUTER Table Joins
SQL allows tables to be joined using the LEFT OUTER and RIGHT OUTER join methods. For example, the ORDERS table in Figure 1 shows no entries for the CUSTNUM of 123-0004. If the user wishes to list all customers, even those that have placed no orders, then the normal join will not return any information on this customer, as the SQL joins the CUSTNUM in the ORDERS table with the CUSTNUM in the CUSTOMERS table. In this case, a LEFT OUTER JOIN should be used:
SELECT *
FROM CUSTOMER LEFT OUTER JOIN ORDERS
etc.
This query will return a line showing all customers, including customer 123-0004, with no orders against him.
SQL UNION Command
The UNION command is used to "add" or concatenate the results of more than one query. For example, if the user wants to know on which dates orders were placed or shipped, the UNION command could be used like this:
SELECT DISTINCT ORDERDATE
FROM ORDERS
WHERE ORDERDATE > SYSDATE - 31
UNION
SELECT DISTINCT SHIPDATE
FROM ORDERS
WHERE ORDERDATE > SYSDATE - 31
This would give a list of any date on which any order was placed, or any order was shipped, in the last 31 days. The two tables used in each SELECT statement may be different, but the fields must be the same type.
SQL DISTINCT, OUTER JOIN and UNION Statement Summary
After the basic SELECT FROM WHERE SQL commands have been used, it is a short step up to use some more powerful commands such as DISTINCT, LEFT OUTER JOIN, and UNION. These make SQL a much more formidable tool with which to query and organise information from database tables. Other functions are available to manipulate strings found by SQL.
SQL References
There are many good courses and references available that describe how to embed SQL into other applications, both online and at most major bookstores. " PHP & SQL For Dummies " is good for beginners.
Martin Bell - Martin holds a B.Sc. degree in chemical engineering, and an M.Sc. degree in electronics and computing. He has spent more than 25 years ...
Join the Conversation