The JOIN keyword examines data in two related tables and uses the matches to return results. The user must provide the columns on which to perform a JOIN. There are multiple types of JOIN commands and each returns slightly different result sets.
Requirements for using JOIN
To use JOIN in a query statement, the query writer must know the structure of the tables. There must be at least one column of data between the two tables which matches for a JOIN to work properly. This information must be provided to the SQL server as part of the query.
Generally, the data involved in a JOIN operation is composed of a “primary” key value in one table and a “foreign” key value in another. A primary key is usually a unique identity value in a table. A foreign key exists in a table which is in some way related to the unique record in the table holding the primary key.
Left JOIN and Right JOIN
There are multiple types of JOIN statements. Each has a slightly different function. JOIN commands refer to the order in which the data is merged together. One of the most common JOIN statements is a LEFT JOIN. In a LEFT JOIN, all data from the table with the primary key value are returned as well as rows from the table holding the foreign key if there is a match. Rows in the foreign key table that do not match a value in the primary key table are ignored.
A RIGHT JOIN is very similar to a LEFT JOIN. As the name suggests, this JOIN type returns the exact opposite result of a LEFT JOIN. The RIGHT JOIN will return all rows from the table holding the foreign key in addition to rows from the primary key table that match. Rows from the primary key table that do not have a corresponding foreign key value are ignored.
Inner, Full, and Cross JOIN
An INNER JOIN statement will compare the data in both tables and return only those rows from each table where a match has occurred. Rows in both the primary key table and foreign key table that do not have a corresponding row in the other table will be ignored. A FULL JOIN is the opposite of an INNER JOIN. A JOIN of this variety returns all rows from each table, aligning data when a match exists.
Another variety of JOIN types, though used less commonly, is the CROSS JOIN. A CROSS JOIN ignores the key values provided and JOINS every row of the primary table with every row of the secondary table. This produces a Cartesian result set. This type of JOIN could be useful for viewing all possible combinations of cars and car paint colors, as an example.
Using the JOIN Statement
To use the JOIN statement, begin by identifying which column in each table has the related data. Set up the query statement as desired. Use whichever table is most appropriate in the “FROM” clause. After the FROM clause, type the desired JOIN type. The format of the JOIN statement is [JOIN Type] ON [primary key table.column] = [foreign key table.column]. This can be followed by any where clauses or order by statements. Cross JOIN statements do not require the ON portion of the clause.
Why Use a JOIN statement
JOIN statements are very powerful in SQL. They allow advanced queries to combine results from multiple tables in one statement. Understanding each type of join is an integral part of programming in SQL.