An SQL Join makes relational database management easy, thanks to joins that link two or more tables by means of common columns. The LEFT JOIN (or LEFT OUTER JOIN) command takes things a step further...
SQL is a query language that is inherently associated with relational databases. The reason it’s so popular is that its syntax is relatively simple to learn, paving the way for the easy creation and manipulation of tables and, from there, databases linking these tables. Its strength lies in its ability to extract very precise information from vast databases.
However, one of the strengths of this language lies in its ability to establish joins between data tables. What does this mean? By combining data from several tables, we can obtain cross-references of particular interest to the data scientist.
Joins are therefore an essential element of SQL. But there are two types of joins you can think of…
What are the 2 types of the SQL JOIN?
The two types of joins are internal and external. What exactly are they?
- Internal (INNER): this is a join that extracts data based on column correspondences between two tables, according to a condition. For example, two or more tables can be associated on the basis of the column name, city, product_number… Thus, if we have a table that lists the films in a videoclub in great detail (actors, director, year of release) and another, much simpler table that indicates which films have been rented, we could easily obtain a detailed list of films currently on rental. The condition used is usually the equality operator, but it can also be <, <=, >, etc.
- External (OUTER): this join extracts records with matches as in the previous case, but also brings up records with no matches between the 1st, 2nd and other tables. We’ll see below how useful this type of external join can be.
Three types of external joints
We have three types of external SQL joins available.
- LEFT JOIN / LEFT OUTER JOIN: “left” join (with the table following the FROM command)
- RIGHT JOIN / RIGHT OUTER JOIN (with the table to the right of the operator)
- FULL JOIN / FULL OUTER JOIN (to extract rows from 2 tables)
In practice, it’s the LEFT JOIN command that’s most often used, because it’s so obvious, as we’ll see in an example below.
SQL JOIN: The LEFT JOIN
The LEFT JOIN SQL command is the most commonly used external join command. It lists all elements common to two tables, as well as all elements in the left-hand table (the table following FROM).
Let’s consider two tables: Table1 and Table2, with Table1 being the left-hand table. The LEFT JOIN will list all results from the left-hand table (Table1), even if there is no correspondence between the two tables.
The syntax could be as follows:
SELECT *
FROM table1
LEFT JOIN table 2
ON table1.id = table2.id
So the LEFT JOIN command examines every row in table1. If it finds a match, it displays the data found in table2. But what does the LEFT JOIN command do when there is no match between the two tables? It returns NULLs for the column in question.
💡Related articles:
What use is the SQL JOIN LEFT JOIN for?
So what’s the point of such an order? Let’s imagine we have two tables. The first, Customers, lists all the company’s customers. The second, Orders, lists all the orders placed by some of these customers.
With an internal join, we could have a list of all customers who have placed one or more orders.
Suppose, however, that we wanted to know the status of all the company’s customers, whether or not they had placed an order. We could obtain this type of information with LEFT JOIN.
This would enable the company to identify registered customers who have never placed an order, and to target them specifically with promotional offers, or to find out why they have never been interested in the company’s products or services.
