Selecting Data – Part 2

The SELECT reserved word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data.

Today, I am continuing our exploration of this reserved word by reviewing queries that merge data using joins and unions.

Six known join types exist in SQL Server. The definitions are based upon joining TABLE A (left) with TABLE B (right).

1. Inner Join – Include matching rows from left and right tables equal joined on a key.
2. Left Outer Join – Include all rows from left table and any matching rows from right table. Unmatched right rows are shown as null values.
3. Right Outer Join – Include all rows from right table and any matching rows from left table. Unmatched left rows are shown as null values.
4. Full Outer Join – Include all rows from left and right tables. Unmatched rows are shown as null values.
5. Theta Join – Match rows between left and right tables using not-equal condition (<, >, <=, >=, <>).
6. Cross Join – This is known as a cartesian product. Each row in left table is matched with all rows in right without any conditions.

We will be creating a simple AUTO database with two tables to show how joins work. TABLE A has cars that Alan owns and TABLE B has cars that John owns. This example does not conform to Normalization rules but defines primary keys for joining between the two tables. In the real world, one table would have a PRIMARY key and the other, a FOREIGN key.

The code snipet below creates this teaching database and populates it with data.

A venn diagram is an easy way to represent set operations. Click on the links below to see diagrams for selected joins. The empty set is when no records are in common between SET T1 and SET T2.

A INNER JOIN finds the records in common between TABLE A and TABLE B.

A LEFT OUTER JOIN list all records that TABLE A has and matching records from TABLE B.

A RIGHT OUTER JOIN list alls records that TABLE B has and matching records from TABLE A.

A FULL OUTER JOIN is basically the UNION of a LEFT and RIGHT outer join with duplicates removed.

A CROSS JOIN is the cartesian product of TABLE A and TABLE B.

A THETA JOIN is when the join condition is a non-equality. I have never used this join in the REAL WORLD; However, If you worked at Chucky Cheese and wanted to see all the items that your kid could get for earned game points, this join might come in handy. From the KIDS table, earned points would be a key that would be joined to the PRIZES table on point value where prize point value <= earn points. This query would give you the prize pool to pick from.

The UNION reserved word is used to join two simular sets of columns from two or more tables.

The default behavoir of the word is to remove duplicates. If you want to keep duplicates, add the ALL reserved word.

The EXCEPT and INTERSECT reserved words are used to merge and compare records as a whole. You probably do not realize we did just this with the LEFT and RIGHT joins on key values.

The two snipets below use the EXCEPT clause to return unique cars.

The snipet below uses the INTERSECT clause to return common cars.

To demonstate that the whole record, not just the PRIMARY key is used in the compare, I am going to change one column to the value test. As we can see, the results change from above. The snipet below restores the data to leave our example intack.

To recap this discussion, JOINS are used to merge data horizontally by adding additional fields to the result set. UNIONS are used to merger the data vertically by stacking simular data together. The EXCEPT and INTERSECT operations return what is different and common between records from two tables. In short, these key words allow you to take NORMALIZED data combine it to return results that are important to your organization.

Next time I talk about the SELECT statement, I will be introducing functions that can be applied to the various datatypes.