CODEWITHSUNDEEP
mysql
Sanjiban Bairagya
Sanjiban Bairagya

Reputation: 744

mysql query select from one table such that its column's values are not present in the same column's values in another table

I have two mysql tables: A, and B.

A has columns id, alpha, blabla, moreblabla, with primary key id. B has columns id, alpha, beta, somemoreblabla, with primary key (id, alpha)

I need to select all those A.id's, for which its A.alpha is not present in any of the B.alpha's respective to every B.id = A.id

How do I do it?

Upvotes: 0

Views: 82

Answers (4)

Gaurav Lad
Gaurav Lad

Reputation: 1808

Select A.id from table_A where A.alpha NOT IN (Select B.alpha from table_B);
The Sub-query will return a table with all the records of B.alpha from table_B. And say it returns 1 to 5 records as result. And say your table table_A has 1 to 10 records in A.alpha Now your parent query will check for records in table table_A for field A.alpha which do not belongs to B.alpha (using NOT IN) Hence the expected result is 6 to 10 as result.

Upvotes: 0

Subin Chalil
Subin Chalil

Reputation: 3660

SELECT A.id
FROM
A 
LEFT OUTER JOIN B  ON A.id = B.id AND B.alpha = A.alpha
where B.alpha IS NULL

Here is SQL Fiddle

This will be the fastest query,better than The marked Answer in terms of query optimization.

EXPLAIN SELECT A.id
        FROM
        A 
        LEFT OUTER JOIN B  ON A.id = B.id AND B.alpha = A.alpha
        where B.alpha IS NULL

Here is the output : enter image description here

EXPLAIN select A.id 
FROM A where A.alpha NOT IN (
    select B.alpha FROM B where B.id = A.id
)

Here is the EXPLAIN of Marked answer.

enter image description here

You can see the DIFFERENCE.

SIMPLE VS PRIMARY

SIMPLE VS DEPENDENT SUBQUERY

Hope this helps.

Upvotes: 1

hoppa
hoppa

Reputation: 3041

Your question is not entirely clear. Do you want all A.alpha's that are not in any B.alpha? In that case a simple query like this is enough:

Select A.id from A where A.alpha NOT IN (Select B.alpha from B);

If you want to select all ID's from A that have a counterpart (an equal ID) in B but where the alpha between A and B are different it is a bit more work:

SELECT A.id FROM A
INNER JOIN B on A.id = B.id
WHERE A.alpha != B.alpha

Consider the following structure:

CREATE TABLE `A` (
`id` int(11) NOT NULL,
`alpha` varchar(255) NOT NULL
);

CREATE TABLE `B` (
`id` int(11) NOT NULL,
`alpha` varchar(255) NOT NULL
);

With inserts:

insert into A set id = 1, alpha = 'a';
insert into A set id = 2, alpha = 'b';
insert into B set id = 1, alpha = 'a';
insert into B set id = 2, alpha = 'a';

If you run the query with the join your result will be:

+----+
| id |
+----+
|  2 |
+----+

This is since ID 2 in A has a different alpha than ID 2 in B.

EDIT:

It just occurred to me that you might even mean that every A.id can occur in B multiple times. If that is what can happen you need a different approach again. Lets assume the same insert as before with an addition:

insert into A set id = 1, alpha = 'a';
insert into A set id = 2, alpha = 'b';
insert into B set id = 1, alpha = 'a';
insert into B set id = 2, alpha = 'a';

insert into B set id = 2, alpha = 'b'; <- important since there is now a 2nd 2 in B that should ensure that the record with ID 2 from A should not be returned.

insert into A set id = 3, alpha = 'c';
insert into B set id = 3, alpha = 'x'; <-- only ID 3 should now be returned due to the situation above

Our tables now look like so:

A
+----+-------+
| id | alpha |
+----+-------+
|  1 | a     |
|  2 | b     |
|  3 | c     |
+----+-------+

B
+----+-------+
| id | alpha |
+----+-------+
|  1 | a     |
|  2 | a     |
|  2 | b     |
|  3 | x     |
+----+-------+

If this is your case the following query will do the trick:

select A.id 
FROM A where A.alpha NOT IN (
    select B.alpha FROM B where B.id = A.id
);

Upvotes: 1

Abhishek Sharma
Abhishek Sharma

Reputation: 6661

Use MySQL NOT IN Try this query :- 

select id from a where a.id NOT IN (select id from b)

Upvotes: 0

Related Questions