CODEWITHSUNDEEP
mysql
Andre Calenta
Andre Calenta

Reputation: 87

Select null if record doens't exist mysql

I want to select a value from a third_table where something from the first_table equals another thing from the third_table. This last value from the third table might not exist!

I have the following mysql query:

$query = "SELECT ft.value1, ft.value2, st.value3, st.value4, tt.valueWantedMightNotExist 
          FROM first_table ft, second_table st, third_table tt 
          WHERE ft.value5 = st.value5" AND ft.something = tt.anothersomething;

This query returns results only if the value exists. Otherwise it returns nothing. I tried this with FULL JOINas well but ended messing up as well. Can someone explain to me how can I return a null value if the record doesn't exist? Thank you very much...

Upvotes: 0

Views: 1724

Answers (2)

chiastic-security
chiastic-security

Reputation: 20520

You need a LEFT JOIN. This will do exactly what you want.

By default, (i.e., if you just use a comma,) you get an INNER JOIN, which will give you rows only when they match on both sides; but a LEFT JOIN will give you everything on the left, and nulls on the right where there's no match.

There's also a RIGHT JOIN, the purpose of which is left as an exercise for the astute reader :)

You should look at this question. The top-voted answer uses LEFT OUTER JOIN, but in MySQL at least, the OUTER has no effect. And the second-highest answer has a pretty picture that's rather helpful for the intuition.

Upvotes: 3

Barmar
Barmar

Reputation: 781503

You need to use LEFT JOIN if you want to get rows with no match.

SELECT ft.value1, ft.value2, st.value3, st.value4, tt.valueWantedMightNotExist
FROM first_table ft
INNER JOIN second_table st ON ft.value5 = st.value5
LEFT JOIN third_table tt ON ft.something = tt.anothersomething

Upvotes: 3

Related Questions