j.Doe j.Doe - 4 months ago 6
SQL Question

JOIN the table if records exist

is it possible if i want to do INNER JOIN only if the record exist on the 2nd table if not then dont join?

this is my table

User table

+--------+--------------+
| id | name |
+--------+--------------+
| 1 | John |
+--------+--------------+
| 2 | Josh |
+--------+--------------+


House table

+--------+-------------+--------------+
| id | owner_id | house_no |
+--------+-------------+--------------+
| 1 | 1 | 991 |
+--------+-------------+--------------+


this is my INNER JOIN query

SELECT h.owner_id, u.name, h.house_no FROM user u
INNER JOIN house h on u.id = h.owner_id
WHERE u.id = :id


it will return this result if
id = 1


+--------+--------------+--------------+
| id | name | house_no |
+--------+--------------+--------------+
| 1 | John | 991 |
+--------+--------------+--------------+


but if i run with
id = 2
no result returned.

what i want to do right now is it still return the result even when no data exist for
id = 2
in table house

Answer

Use a left outer join instead.

SELECT u.id, u.name, h.house_no FROM user u
LEFT OUTER JOIN house h on u.id = h.owner_id 
WHERE u.id = :id

The resulting record will be:

+--------+--------------+--------------+ 
|   id   |     name     |   house_no   |
+--------+--------------+--------------+
|   2    |     Josh     |     null     | 
+--------+--------------+--------------+
Comments