How to Returns all rows from the left table, and the matching rows from the right table in SQL ?
To return all rows from the left table and the matching rows from the right table in SQL, we can use the LEFT JOIN
or LEFT OUTER JOIN
clause.
The syntax for LEFT JOIN
is as follows:
SELECT *
FROM left_table
LEFT JOIN right_table
ON left_table.key = right_table.key;
Here, left_table
is the name of the left table, right_table
is the name of the right table, and key
is the column that is used to join the two tables.
For example, let's say we have two tables students
and grades
with the following data:
students table:
id name age
1 Alice 20
2 Bob 22
3 Charlie 21
grades table:
id grade
1 A
2 B
4 C
To return all rows from the students
table and the matching rows from the grades
table, we can use the following query:
SELECT *
FROM students
LEFT JOIN grades
ON students.id = grades.id;
This will give us the following result:
id name age grade
1 Alice 20 A
2 Bob 22 B
3 Charlie 21 NULL
Note that the row for Charlie is included in the result, even though there is no matching row in the grades
table. This is because we used a LEFT JOIN
, which returns all rows from the left table (students
) even if there is no matching row in the right table (grades
).
We can also use the LEFT OUTER JOIN
clause instead of LEFT JOIN
. The syntax is the same, but OUTER
is optional and can be omitted. The result will be the same as using LEFT JOIN
.
SELECT *
FROM students
LEFT OUTER JOIN grades
ON students.id = grades.id;