Using information from one table to find if the info exists in another table

Question

I have 2 tables:

ads :

+------+---------------+
|  ID  |  Name         |
+------+---------------+
| 1    |   Item 1      |
| 2    |   Item 2      |
| 3    |   Item 3      |
| 4    |   Item 4      |
| 5    |   Item 5      |
+------+---------------+

assigned_ads :

+------+-----------+--------------+
|  ID  |   ad_id   |  location    |
+------+-----------+--------------+
|  1   |    3      |  place 1     |
|  2   |    2      |  place 2     |
|  3   |    1      |  place 3     |
+------+-----------+--------------+

As you can see; ad 1,2 and 3 are all assigned to a location. What I need is a query that gets all the the rows in the ads table that isn't assigned in the assigned_ads table

Answer 1

You need to use a LEFT OUTER JOIN to do this, as show below:

SELECT * 
FROM ads a
LEFT JOIN assigned_ads aa ON a.ID = aa.ad_id
WHERE aa.location IS NULL

Answer 2

You can also use the NOT EXISTS operator:

SELECT * 
FROM ads a
WHERE NOT EXISTS
(SELECT * FROM assigned_ads aa WHERE a.ID = aa.ad_id)

Answer 3

The relational operator you require is semi difference a.k.a. antijoin.

Most SQL products lacks an explicit semijoin operator or keyword. Standard SQL-92 doesn't have one (it has a MATCH (subquery) semijoin predicate but, although tempting to think otherwise, the semantics for NOT MATCH (subquery) are not the same as for semi difference; FWIW the truly relational language Tutorial D successfully uses the NOT MATCHING semi difference).

Semi difference can of course be written using other SQL predicates. The most commonly seen are: outer join with a test for nulls in the WHERE clause, closely followed by EXISTS or IN (subquery). Using EXCEPT (equivalent to MINUS in Oracle) is another possible approach if your SQL product supports it and again depending on the data (specifically, when the headings of the two tables are the same).

Personally, I prefer to use EXISTS in SQL for semi difference join because the join clauses are closer together in the written code and doesn't result in projection over the joined table e.g.

SELECT *
  FROM ads 
 WHERE NOT EXISTS (
                   SELECT * 
                     FROM assigned_ads
                    WHERE ads.ID = assigned_ads.ID
                  );

As with IN (subquery) (same for the outer join approach), you need to take extra care if the WHERE clause within the subquery involves nulls (hint: if WHERE clause in the subquery evaluates UNKNOWN due to the presence of nulls then it will be coerced to be FALSE by EXISTS, which may yield unexpected results).

Answer 4

try this:

SELECT * 
FROM ads a
WHERE a.id NOT IN (SELECT ad_id FROM assigned_ads)

Answer 5

just user left join query and match the left table id to null. you query should look like this

select * from ads ad left join assigned_ad a_ad ON a_ad.ad_id = ad.id where a_ad.id is null

For more information about left join consult here