Search code examples
mysqlsqldatabasedrupalright-join

How to do a right outer join in MySQL?

'SELECT * FROM (SELECT * FROM t1
          RIGHT OUTER JOIN t2 ON t1.wid = t2.wid
          WHERE t2.wid IS NULL)
          LIMIT ' . $number;

This SQL lines do not give not one row back and I do not know the reason. The t1 is full of rows while the t2 is an empty table.

Solution

  • Given that you need to do a LEFT OUTER JOIN to retrieve the rows in t1

    You will also need to avoid duplicate column names in the inner result before you make the outer select, eg SELECT t1.*

    Also you may need to give an alias to the derived table in the inner select eg t3 ...

    SELECT t3.* FROM (
    SELECT t1.* FROM t1
    LEFT OUTER JOIN t2 ON t1.wid = t2.wid
    WHERE t2.wid IS NULL)
AS t3;