CODEWITHSUNDEEP
mysqlsql
sang oh
sang oh

Reputation: 66

mysql - why this subquery on WHERE clauses works?

my movie table is here,

title        year length incolor studioName producerC#

Avengers        2012    142 1   Marvel      12345
First Avenger   2011    123 1   Marvel      38664 
Justice League  2017    121 1   DC          11223 
Man of Steel    1925    142 1   DC          38664 
Man of Steel    2013    142 1   DC          11223 
Ted             2012    106 1   Universal   87654 
Toy Story 3     2010    102 1   Pixar       23456

and i want find “the titles that have been used for two or more movies.”

the answer is 'Man of Steel' because this movie's year is 1925, 2013 so it used twice.

and my query is here.

select distinct movie.title 
from movie, (select * from movie) as temp
where temp.title = movie.title 
    AND (temp.year != movie.year OR temp.length != movie.length OR temp.studioName != movie.studioName);

as respect, the reult is Man of Steel what i want.

but i don't understand difference of temp, movie.

both two table has perfectly same value, so temp.title = movie.title should be select title from movie;

and temp.year != movie.year should be null because temp.year != movie.year is same of select year from movie where movie.year != movie.year

but result is 'Man of Steel' and i can't understand what i missed.

Upvotes: 1

Views: 78

Answers (2)

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

In your query, both Temp and Movie are the exact same tables and the purpose of that join in other terms SELF JOIN is to match two movie titles while at least one of the other characteristic differs it can be Year, Length Or Studio Name. That's why it is given in the WHERE statement with a not equal notation in OR Condition.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

Your current query is suboptimal. You should rewrite it using explicit joins, in this case, an explicit self-join:

SELECT DISTINCT
    m1.title
FROM movie m1
INNER JOIN movie m2
    ON m1.title = m2.title
WHERE
    m1.year <> m2.year OR m1.length <> m2.length OR m1.studioName <> m2.studioName;

The logic here is to consider every pair of movies which share the same title, but do not share either the year, length, or studio name. That is, find all movies titles such that another movie exists with the same title, but one or more of the other fields differ.

There is another way to write your query using aggregation by movie title:

SELECT title
FROM movie
GROUP BY title
HAVING
    MIN(year) <> MAX(year) OR
    MIN(length) <> MAX(length) OR
    MIN(studioName) <> MAX(studioName);

Upvotes: 3

Related Questions