SQL JOIN USING and WHERE

Question

This is somewhat of a followon to SQL JOIN where to place the WHERE condition?

I would prefer to use the USING clause on the join, but am not yet able to put the field value condition with the join.

SELECT 1
  FROM table1 t1
  JOIN table2 t2 USING(id) AND t2.field = 0;

ORA-00933: SQL command not properly ended

Is it possible to have USING and another condition as part of the JOIN clause?

The accepted answer that you link to is poor. See my comment on it. — philipxy, Jun 27 '16 at 12:24

score 0 · Answer 1 · edited May 23 '17 at 11:58

You can use:

SELECT 1
FROM table1 t1
JOIN table2 t2 USING(id)
WHERE t2.field = 0;

Using USING(id) is like using ON t1.id = t2.id except that in the JOIN result instead of two columns t1.id & t2.id there is only one id column.

For INNER JOIN USING with a condition followed by an OUTER JOIN you need a subquery to keep the WHERE with the USING:

SELECT ...
FROM (SELECT id, ...
    FROM table1 t1
    JOIN table2 t2 USING(id)
    WHERE t2.field = 0) s
LEFT JOIN ...;

For an OUTER JOIN USING with a condition you need a subselect:

SELECT ...
FROM table1 t1
LEFT JOIN (SELECT *
    FROM table2 t2
    WHERE t2.field = 0) t2
USING (id);

See this re ON/WHERE with JOIN. See this re ON/WHERE when mixing INNER & OUTER JOINs.

SQL JOIN USING and WHERE

1 Answers1