How to select the records from one table which is not matching with the other two tables

Question

I have one master table emp1 table with 3 columns and 3 rows as you can see below

empid empname empsal
1     a       100
2     b       300
3     c       500

second table emp2 with 3 columns and 1 row

empid empname empsal
1     a       100

suppose, if I have to pick the records which are only available in the first table but not in second table then I can use the below query

select * FROM emp1 b 
  LEFT JOIN emp2 f ON f.empid = b.empid 
      WHERE f.id IS NULL

but here I have third table emp3 with 3 columns and 1 row

empid empname empsal
2     b       300

if I have to pick the records which are available in the first table only, but not in second and third table, what could be the best solution?

Add another left join to emp3 and add a condition to the where clause " AND emp3.id is NULL" — codemonkey, Nov 19 '17 at 00:06
@RafaelNadal Yes you can have it, which also you can mark it as accepted if it works and solve your issue. — Ilyes, Nov 19 '17 at 00:17
actually I am new here, sorry for that, however, in the above question I have mention it in a simple way, but the thing is second and third table don't have the same number of columns as first table has — Rafael Nadal, Nov 19 '17 at 00:39
@RafaelNadal It's ok, but please read **[How to ask](https://stackoverflow.com/help/asking)** and take the **[tour](https://stackoverflow.com/tour)** :) — Ilyes, Nov 19 '17 at 01:01

score 0 · Accepted Answer · answered Nov 19 '17 at 00:13

0

There is many ways to do it, here is one using NOT IN:

CREATE TABLE Emp1 (
    empid INT, 
    empname VARCHAR(25),
    empsal INT
    );

CREATE TABLE Emp2 (
    empid INT, 
    empname VARCHAR(25),
    empsal INT
    );

CREATE TABLE Emp3 (
    empid INT, 
    empname VARCHAR(25),
    empsal INT
    );

INSERT INTO Emp1 VALUES
(1,     'a',       100),
(2,     'b',       300),
(3,     'c',       500);

INSERT INTO Emp2 VALUES
(1,     'a',       100);

INSERT INTO Emp3 VALUES
(2,     'b',       300);

SELECT *
FROM Emp1
WHERE empid NOT IN ( SELECT empid FROM Emp2 UNION SELECT empid FROM Emp3);

Results:

+-------+---------+--------+
| empid | empname | empsal |
+-------+---------+--------+
|     3 | c       |    500 |
+-------+---------+--------+

Demo

answered Nov 19 '17 at 00:13

Ilyes

14,640
4
29
55

@RafaelNadal Glad to help :) – Ilyes Nov 19 '17 at 00:27
Actually, I have more than 30 millions records in the first table and I need to check with other 25 tables with that first table, in this case, what could be the best solution based on performance, thanks! – Rafael Nadal Nov 19 '17 at 00:32
@RafaelNadal I think I see that ^ question before, and you get solutions for it, and that what was Mitch Wheat refering to [here](https://stackoverflow.com/questions/47372361/how-to-select-the-records-from-one-table-which-is-not-matching-with-the-other-tw/47372466?noredirect=1#comment81697631_47372361) – Ilyes Nov 19 '17 at 00:34
Suppose, if I don't have same number of columns in the other two tables (emp2 has only 4 columns and emp3 has 5 columns) then what could be the solution?? anyone please help!! – Rafael Nadal Nov 19 '17 at 00:51
@RafaelNadal Did I use the other columns in my answer? No, you don't need them even Emp2 table has 5 columns and Emp3 has 10 columns, the important thing is the ID. Try to play with the demo included. – Ilyes Nov 19 '17 at 00:59

How to select the records from one table which is not matching with the other two tables

1 Answers1