CODEWITHSUNDEEP
mysql
mort
mort

Reputation: 83

JOIN - no matter the order

How can i join multiple tables not depending on LEFT or RIGHT join

example:

t1 
        id | date
       -----------
       NULL  NULL

t2 
        id | value
       ------------
        1  |  bla

SELECT date,value 
FROM t2 LEFT JOIN t1 
    ON t1.id = t2.id where t2.id = 1

-- select is ok

with right join same query return null values ...

Upvotes: 0

Views: 63

Answers (2)

Nick Rolando
Nick Rolando

Reputation: 26167

SELECT `date`, `value` FROM t2 FULL OUTER JOIN t1 ON t1.id = t2.id

Edit: That will return all records, even if there is no match on those joining fields. Sorry, that will give you syntax error. See my edit down below.

If you only want matches, use inner join:

SELECT `date`, `value` FROM t2 INNER JOIN t1 ON t1.id = t2.id

Edit: There is no FULL OUTER JOIN in MySQL. You will have to simulate it with UNION and combine a LEFT and RIGHT JOIN:

SELECT `date`, `value` FROM t2 LEFT JOIN t1 ON t1.id = t2.id
UNION
SELECT `date`, `value` FROM t2 RIGHT JOIN t1 ON t1.id = t2.id

This will also return your NULL values, but they will not match, as NULL != NULL

Upvotes: 2

Victor
Victor

Reputation: 163

You must use FULL OUTER JOIN synstax. In fact Left JOIN returns all results in Left column, whereas right join returns all results in right column. SO with full outer, both are returned.

SELECT date,value FROM t2 FULL OUTER JOIN t1 ON t1.id = t2.id where t2.id = 1

Upvotes: 0

Related Questions