Can Somebody help me understand this query

Question

The below SQL query gets all the ids which have different last names.

select *
from names a
WHERE a.first_name in (
select b.first_name 
  from names b
  WHERE a.last_name<>b.last_name
)

I am not able to figure out how this exactly works. What I thought would happens is, for every row in the table the subquery will check if there exists a row with a different last name. But the checking happens among only identical values of first name. Can someone explain how this actually works.

Answer 1

The SQL given is an example of a correlated subquery.

It is correlated because there is a reference to the outer table inside the subquery.

Some people find this form more intuitive than writing the equivalent join or EXISTS. Others find it a bit weird and more difficult to think about.

SQL often allows us to write the same logical query specification in different ways. Given a good optimiser, which you choose comes down to a question of style. Not all optimisers are good.

In this case, the correlation is the reference to a.last_name. This makes the result of the subquery dependent on the 'current row' from the outer query.

Essentially, the query is asking for rows where the first_name attribute matches any row (from the same table) where the last_name attribute is different.

In other words, people who share a first name but not a surname.

You could write the same requirement as:

SELECT * 
FROM names AS a
WHERE EXISTS
(
    SELECT * 
    FROM names AS b
    WHERE b.first_name = a.first_name
    AND b.last_name <> a.last_name
);

Some people might find that easier to read than the IN subquery form. It does make it easier to see there are two tests involved, perhaps.

See 13.2.11.7 Correlated Subqueries in the reference manual.

Answer 2

The below SQL query gets all the ids which have different last names.

No it doesn't.

Why the query in the question doesn't return all the names which have different last names?

Because it doesn't take in consideration same names.

In simple words in the example below, which are the names that have different last names ?

Answer. John and Anna.

You are selecting the rows with first_name in (John,Anna) , but with this condition the rows John Smith and Anna Smith are returned.

Follow below example:

CREATE TABLE names (
`first_name` VARCHAR(15),
`last_name` VARCHAR(15) );

INSERT INTO names VALUES
('John','Smith'),
('John','Test2'),
('Anna','Maria'),
('Anna','Smith'),
('Ron','Smith'),
('Maria','Smith');

Based on the SQL query gets all the ids which have different last names. the query in the question must return only below values,

first_name  last_name
John    Test2
Anna    Maria

but it doesn't, it gives:

first_name  last_name
John    Smith
John    Test2
Anna    Maria
Anna    Smith

Why is wrong result returned ?

When you need to select names which have different last names you have to use a condition where last_names count is equal to 1.

Maybe you are trying to use:

select * 
from names 
where last_name in ( select last_name
                     from names
                     group by last_name
                     having count(*) = 1
                   )

which will return only the names that doesn't share last_names

Check the fiddle.