SQL Tutorial

Previous | Home  | Next  

SQL Keywords

Introduction
FROM, SELECT, WHERE, DISTINCT
ORDER BY
AGGREGATE FUNCTIONS
GROUP BY
HAVING
INNER JOIN
LEFT JOIN

Examples

A structured approach to writing queries
Example 1
Example 2
Example 3
Example 4
Example 5
Example 6
SQL course - Part 6

LEFT JOIN

In the previous section, it was explained that information from different tables can be combined by working with an INNER JOIN. In this linking method, the connection between the two tables can be established by mentioning the name of the tables to be combined after "FROM" as well as the name of the columns that contain the foreign key and primary key after "WHERE".

Importantly, when tables are combined using an INNER JOIN, records will only be merged when there actually is a matching primary key for the foreign key of that record. This is no problem if it is specified in the DBMS that the foreign key column must have a value, and if the DBMS also enforces referential integrity. In some cases, however, it may have been specified that the column containing the foreign key can have missing values. When no matching combination of primary and foreign key can be found, the records will be ignored entirely. This is not always desirable, because some of the other columns may still contain useful data.

To solve such problems, you can work with a LEFT JOIN. The syntax of the LEFT JOIN is as follows:

SELECT [ column(s) ]
FROM [ table1 ]
LEFT JOIN [ table2 ]
ON table1.column =table2.column ;

As is the case for the INNER JOIN, you mention the two columns that contain the foreign key and the primary key. These columns are mentioned after the "ON" keyword. The names of the tables are joined using the "LEFT JOIN" keyword. A crucial difference with INNER JOINS is that this query will always show all the records from table1. The supplementary data from table2 will be shown when a matching value is found for the value provided in the foreign key column. If not, the query will return blank columns for that second table.

The LEFT JOIN, in conclusion, ought to be used when you want to ensure that all the records are shown and that you do not miss any data.

[ up  | next ]