Re: Returning values that are in one table, but not in another

[Felix Geerinckx]

Gareth Innes wrote:

> I'm looking for a query to return values which occur in one table, but not in the other.
>
> Say we have two tables (table_1, table_2) each with a column titled "appointments".
>
> I want a list of results for appointments which are in table_1, but not in table_2

You can use a LEFT OUTER JOIN (or LEFT JOIN for short):

SELECT table_1.appointments
FROM table_1
LEFT JOIN table_2 ON table_2.appointments = table_1.appointments
WHERE
table_2.appointments IS NULL

This query joins table_1 with table_2, taking all rows from table_1 and only those rows from table_2 for which the ON condition is true. If there is no match in table_2, it returns NULL for the columns of table_2. The WHERE clause therefore makes sure that only those rows are returned where there is no match in table_2.

--
felix
Please use BBCode to format your messages in this forum.