MySQL "Not In" Query 3 Tables

MySQL “NOT IN” query 3 tables

Avoid NOT IN like the plague if

SELECT ID_Courses FROM Evaluation where `NAME`='JOHN' and Year=1

could ever contain NULL. Instead, use NOT EXISTS or Left Joins

use explicit joins, not 1980's style joins using the WHERE clause

To illustrate the misery of NOT IN:

SQL NOT IN () danger

create table mStatus
(   id int auto_increment primary key,
    status varchar(10) not null
);
insert mStatus (status) values ('single'),('married'),('divorced'),('widow');

create table people
(   id int auto_increment primary key,
    fullName varchar(100) not null,
    status varchar(10)  null
);

Chunk1:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single');
select * from mstatus where `status` not in (select status from people);

** 3 rows, as expected **

Chunk2:

truncate table people;
insert people (fullName,`status`) values ('John Henry','single'),('Kim Billings',null);
select * from mstatus where status not in (select status from people);

no rows, huh?

Obviously this is 'incorrect'. It arises from SQL's use of three-valued logic,
driven by the existence of NULL, a non-value indicating missing (or UNKNOWN) information.
With NOT IN, Chunk2 it is translated like this:

status NOT IN ('married', 'divorced', 'widowed', NULL)

This is equivalent to:

NOT(status='single' OR status='married' OR status='widowed' OR status=NULL)

The expression "status=NULL" evaluates to UNKNOWN and, according to the rules of three-valued logic,
NOT UNKNOWN also evaluates to UNKNOWN. As a result, all rows are filtered out and the query returns an empty set.

Possible solutions include:

select s.status
from mstatus s
left join people p
on p.status=s.status
where p.status is null

or use not exists

SQL query NOT IN from 3 tables with conditions

I managed to work it with another parameter.

SELECT * FROM (SELECT `email`, `secretId` FROM `old_data`
WHERE `type` = 'Accounts' AND `email` NOT IN (SELECT `email1` FROM `clients`)) AS t1 
WHERE t1.secretId NOT IN (SELECT `secretId` FROM `trashes` WHERE `type` = 'Accounts')

Also works with

SELECT od.`email`, od.`secretId`
FROM `old_data` od
WHERE od.`type` = 'Accounts' AND
      od.`email` NOT IN (SELECT `email1` FROM `clients`) AND
      od.`secretId` NOT IN (SELECT `secretId` FROM `trashes` WHERE `type` = 'Accounts');

---

Thanks for helping. :)

SQL 'Not in' query involving three tables

Assumption: A product can be part of categories that exist, categories that do not exist, or no categories at all. You have not asked for products that belong to no categories at all, so the first LEFT JOIN from product to procduct_to_category should be an INNER JOIN.

Caveat: I am rusty at mysql so I am using SQL SERVER syntax. I forget if mysql has ON clauses or uses where clauses for JOINs. If ON clause is not supported, change them into WHERE clauses.

There are two common approaches: OUTER JOIN or a NOT IN clause (or a NOT EXISTS clause, which often behaves the same performance-wise as the NOT IN clause.)

1. OUTER JOIN

   select p.*, p2c.category_id

   from product p

   INNER JOIN product_to_category p2c ON (p.product_id = p2c.product_id)

   LEFT JOIN category c ON p2c.category_id = c.category_id

   WHERE c.category_id IS NULL

The test for null will find the unmatched records.

1. NOT IN clause

   SELECT p.*, p2c.category_id

   FROM product p

   INNER JOIN product_to_category p2c ON (p.product_id = p2c.product_id)

   WHERE p2c.category_id NOT IN (SELECT c.category_id FROM category c)

SQL join 3 tables when no matching records exist on 2 tables

Place the where clause after all join like below

If you want to select domains which does not exist in either of the two table keywords and comments, then use OR condition in WHERE clause

SELECT * FROM domains 
LEFT JOIN keywords ON domains.domain_id=keywords.domain_id 
LEFT JOIN comments ON domains.domain_id=comments.domain_id 
WHERE keywords.domain_id IS NULL 
OR comments.domain_id IS NULL

If you want to select the domains which does not exist in both the tables keywords and comments, just replace the OR condition in WHERE clause to AND like below

SELECT * FROM domains 
LEFT JOIN keywords ON domains.domain_id=keywords.domain_id 
LEFT JOIN comments ON domains.domain_id=comments.domain_id 
WHERE keywords.domain_id IS NULL 
AND comments.domain_id IS NULL

MySQL how to join 3 tables when one doesn't have a common key

You can "chain" your joins:

SELECT title, author_last, on_hand
FROM   book b
JOIN   inventory i ON b.book_code = i.book_code
JOIN   wrote w ON b.book_code = w.book_code
JOIN   author a ON a.author_num = w.author_num
WHERE  branch_num = 4

How to select multiple tables in single query mysql? (some tables have no data yet)

All the tables equally have id, first_name, last_name, gender and only deliveries table has their own data. (the other 2 tables are currently empty.)

Now, I want to select all of them in 1 query

I suspect that you are looking for union all:

SELECT first_name, last_name, gender FROM patients
UNION ALL
SELECT first_name, last_name, gender FROM customers 
UNION ALL 
SELECT first_name, last_name, gender FROM deliveries

This will combine all records available in the 3 tables in the resultset. On the other hand, using an (implicit) cross join like you do would generate a cartesian product of the 3 tables, with 9 columns (3 * 3) in the resultset (that is, if you fix the ambiguity on column names that you currently have).

If you want to eliminate duplicates accross tables, you can use union instead of union all.

If you want to limit the number of records in the resultset, you can do this as follows:

(
    SELECT first_name, last_name, gender FROM patients
    UNION ALL
    SELECT first_name, last_name, gender FROM customers 
    UNION ALL 
    SELECT first_name, last_name, gender FROM deliveries
)
ORDER BY id
LIMIT 5000

Note that, functionaly this does require an order by clause, otherwise the ordering of the results is undefined (I assumed id).

Join three tables on condition

We can do LEFT JOIN on B and C tables separately using id. Now, we can utilize COALESCE(), to consider B.bedrooms first (if not null), else C.bedrooms. Now, there is a chance that both can be null (that is, id does not exist in either of the tables).

In that case, if you don't want those rows, you can put an additional WHERE condition to consider only those id(s), which has atleast one matching row in either B or C.

Here is one way:

select A.id,
       A.price,
       COALESCE(B.bedrooms, C.bedrooms) AS bedrooms 
from A 
left join B on A.id = B.id
left join C on A.id = C.id
WHERE NOT (B.bedrooms IS NULL AND 
           C.bedrooms IS NULL)
order by A.id

---

Related Topics