sql left join

sql left join

Posted by Ervin Adams on December 16, 2022

sql left join

sql left join

Posted by Ervin Adams on December 16, 2022

The LEFT JOIN is a clause that allows you to query two or more 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;

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;

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                |
+----+----------+-------------+--------------------------------+

 

Any question?
This field is required
Your question have been successfully submitted and will be reviewed before published
Please login to ask a question