How to Write "Not in ()" SQL Query Using Join

How to write not in () sql query using join

This article:

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

may be if interest to you.

In a couple of words, this query:

SELECT  d1.short_code
FROM    domain1 d1
LEFT JOIN
        domain2 d2
ON      d2.short_code = d1.short_code
WHERE   d2.short_code IS NULL

will work but it is less efficient than a NOT NULL (or NOT EXISTS) construct.

You can also use this:

SELECT  short_code
FROM    domain1
EXCEPT
SELECT  short_code
FROM    domain2

This is using neither NOT IN nor WHERE (and even no joins!), but this will remove all duplicates on domain1.short_code if any.

SQL using Left Join to replace not in clause in a special case

I believe your second attempt is the right way to go (although, I think NOT IN is not so bad, but if this is quicker then I'm all for it):

mysql> SELECT * FROM tableA;
+-------+-------------+
| item  | ReferenceID |
+-------+-------------+
| Chair |         123 |
| Desk  |         456 |
| Sofa  |         789 |
| Bed   |         111 |
+-------+-------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM tableB;
+---------+-------------+
| storeid | ReferenceID |
+---------+-------------+
| 1       |         123 |
| 1       |         456 |
| 2       |         123 |
| 2       |         456 |
| 3       |         111 |
+---------+-------------+
5 rows in set (0.00 sec)

mysql> select * from tableA left join tableB on (tableA.referenceID = tableB.referenceID and StoreID='1') where tableB.StoreID is null;
+------+-------------+---------+-------------+
| item | ReferenceID | storeid | ReferenceID |
+------+-------------+---------+-------------+
| Sofa |         789 | NULL    |        NULL |
| Bed  |         111 | NULL    |        NULL |
+------+-------------+---------+-------------+
2 rows in set (0.00 sec)

---

Updated for tableC inclusion (which has no effect on the results and still works as expected):

mysql> select * from tableA inner join tableC ON tableA.item = tableC.item left join tableB on (tableA.referenceID = tableB.referenceID and StoreID='1') where tableB.StoreID is null;
+------+-------------+------+--------------------+---------+-------------+
| item | ReferenceID | item | ReferenceID        | storeid | ReferenceID |
+------+-------------+------+--------------------+---------+-------------+
| Sofa |         789 | sofa | large sofa (24*12) | NULL    |        NULL |
| Bed  |         111 | bed  | double size        | NULL    |        NULL |
+------+-------------+------+--------------------+---------+-------------+
2 rows in set (0.00 sec)

Convert sub-query with NOT IN operator to join with multiple tables

Alright so you probably shouldn't change this to a join I would use NOT EXISTS the reasons for doing so are stated here

I've also replace your ancient join syntax and added aliases to clear this up. The method shown below has been the accepted standard for about 22 years now and is the preferred way to write queries.

SELECT C.* 
FROM   Consultants  as C -- aliases are very useful for clarity
WHERE  
NOT EXISTS (
        SELECT 1
         FROM   Links as L 
         INNER JOIN Reminders  as R --New join syntax
            ON  L.FromID = R.RemIntID 
         WHERE C.ConsIntID = L.ToID 
         AND ApptSubType = 'Placed' 
         AND ToID LIKE 'CS%'
            ) 

MySQL JOIN ON vs USING?

It is mostly syntactic sugar, but a couple differences are noteworthy:

ON is the more general of the two. One can join tables ON a column, a set of columns and even a condition. For example:

SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...

USING is useful when both tables share a column of the exact same name on which they join. In this case, one may say:

SELECT ... FROM film JOIN film_actor USING (film_id) WHERE ...

An additional nice treat is that one does not need to fully qualify the joining columns:

SELECT film.title, film_id -- film_id is not prefixed
FROM film
JOIN film_actor USING (film_id)
WHERE ...

To illustrate, to do the above with ON, we would have to write:

SELECT film.title, film.film_id -- film.film_id is required here
FROM film
JOIN film_actor ON (film.film_id = film_actor.film_id)
WHERE ...

Notice the film.film_id qualification in the SELECT clause. It would be invalid to just say film_id since that would make for an ambiguity:

ERROR 1052 (23000): Column 'film_id' in field list is ambiguous

As for select *, the joining column appears in the result set twice with ON while it appears only once with USING:

mysql> create table t(i int);insert t select 1;create table t2 select*from t;
Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select*from t join t2 on t.i=t2.i;
+------+------+
| i    | i    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> select*from t join t2 using(i);
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

MySQL: How do I join same table multiple times?

You need to use multiple LEFT JOINs:

SELECT 
    ticket.ticket_id,  
    a1.attr_val AS attr_val1,
    a2.attr_val AS attr_val2,
    a3.attr_val AS attr_val3
FROM ticket
    LEFT JOIN attr a1 ON ticket.ticket_id=a1.ticket_id AND a1.attr_type=1
    LEFT JOIN attr a2 ON ticket.ticket_id=a2.ticket_id AND a2.attr_type=2
    LEFT JOIN attr a3 ON ticket.ticket_id=a3.ticket_id AND a3.attr_type=3

Here is an example: SQL Fiddle.

Joining two tables where id does not equal

SELECT * FROM table1
INNER JOIN table2 ON table2.empid = table1.empid AND table2.alt_id <> table1.alt_id

How do I find records that are not joined?

select * from a where id not in (select a_id from b)

Or like some other people on this thread says:

select a.* from a
left outer join b on a.id = b.a_id
where b.a_id is null

SQL Server join where not exist on other table

The simplest I can think of:

select * from Service
where Id not in (
    select ServiceId
    from AssetService 
    where AssetId = 1);

SQLFiddle link

I don't think it's possible using inner join, because that would only retrieve records that match some criteria and you are looking for records that do not match.

It is, however, possible to do it with left join as Ctznkane525 shows in his answer.

Edit

As jarlh pointed out in the comments, not in might lead to surprising results when there are nulls in the subquery. So, here is the not exists version:

select Id, Name
from Service s
where not exists (
    select *
    from AssetService a
    where AssetId = 1
    and ServiceId = s.Id);

SQLFiddle link

How to use an UPDATE Query with an INNER JOIN to update fields within a table

Your syntax is indeed incorrect for SQL Server - if I understand your last paragraph you just need a conditional case expression. If the following (of course untested) is not correct hopefully it's enough to put you on the right track:

update t1 set t1.Marked =
    case t2.type
      when 'Summary' then 'Yes'
      when 'Full' then 'No'
      else 'N/A'
    end
from tbl_1 t1
left join tbl_2 t2 on t1.PersNo = t2.PersNo;

---

Related Topics