SQL left join

How to create a SQL left join

Posted by Ervin Adams on April 5, 2023

SQL left join

How to create a SQL left join

Posted by Ervin Adams on April 5, 2023

The LEFT JOIN is a clause that allows you to query two tables in sql used to collect data.

Although even if there are no matches in the right table, all the relevant rows will be returned in the left table.

Therefore, the LEFT JOIN returns all the values from the table and matching values from the right table or NULL if there is no match

Here is an example of a LEFT JOIN in sql below:

SELECT tableA.columnA, tableB.columnB
FROM tableA
LEFT JOIN tableB
ON tableA.column = tableB.column;

In this syntax, tableA is the left table and tableB is the right table. The ON clause specifies the join condition, which determines how the two tables are linked.

When the join is performed, all the records from tableA are included in the result set. If there is a matching record in tableB, it is included as well. If there is no matching record in tableB, then the columns from tableB will be null in the result set.

Here is an example:

Table A are students in a school

+----+----------+-----+------------+
| id | name     | age | student_num|
+----+----------+-----+------------+
|  1 | Kevin    |  15 | 078657     |
|  2 | Dane     |  16 | 076565     |
|  3 | James    |  15 | 087675     |
|  4 | Sandy    |  14 | 075657     |
|  5 | Ava      |  16 | 085654     |
|  6 | Zuri     |  15 | 075555     |
+----+----------+-----+------------+

Table B are adminstrations details

+----+-----------+--------+---------------------+------------+
| id | subject   | teacher| date                | student_id |
+----+-------------+----------------------------+------------+
|  1 | Math      | Mr Jack| 2022-10-08 00:00:00 | 1          |
|  2 | English   | Mrs Jen| 2022-10-08 00:00:00 | 1          |
|  3 | Physics   | Mrs Lin| 2021-09-08 00:00:00 | 4          |
|  4 | Geography | Mr Dan | 2020-09-08 00:00:00 | 3          |
+----+-------------+---------+------------------+------------+

Let us do the LEFT JOIN on the tables above:

SELECT id, name, student_num, subject, date
   FROM students
   LEFT JOIN administrations
   ON students.id = administrations.student_id;

In this query, the students table is the left table, and the administrations table is the right table. The ON clause specifies that we want to join the two tables on the student_id column in the administrations table and the id column in the students table.

The result set will include all students, even those who do not have a corresponding department in the administrations table.

Result

+----+----------+-------------+--------------------------------+
| id | name     | student_num | subject  | date                |
+----+----------+-------------+--------------------------------+
|  1 | Kevin    | 078657      | Math     | 2022-10-08 00:00:00 |               
|  1 | Kevin    | 078657      | English  | 2022-10-08 00:00:00 |
|  2 | Dane     | 076565      | NULL     | NULL                |
|  3 | James    | 087675      | Geography| 2020-09-08 00:00:00 |
|  4 | Sandy    | 075657      | Physics  | 2021-09-08 00:00:00 |
|  5 | Ava      | 085654      | NULL     | NULL                |
|  6 | Zuri     | 075555      | NULL     | NULL                |
+----+----------+-------------+--------------------------------+
This field is required
Your question have been successfully submitted and will be reviewed before published
Please login to ask a question