Where And Where SQL

Question

I have a database. I wanted to see Movies which have English and Spanish subtitles. I wanna do sql query like this and It doesn't works.

SELECT name,language FROM DvD 
JOIN Subtitles ON  Dvd.dvdID=Subtitle.dvdID 
where language='EN' and language='ES';

But When I tried to do like this, It works.

SELECT name,language FROM DvD 
JOIN Subtitles ON  Dvd.dvdID=Subtitle.dvdID 
where language='EN' or language='ES';

score 2 · Answer 1 · answered Jun 25 '20 at 01:37

You actually need aggregation if you want DVDs that have both:

SELECT dvd.name
FROM DvD JOIN
     Subtitles s
     ON Dvd.dvdID = s.dvdID
WHERE s.language IN ('EN', 'ES')
GROUP BY dvd.name
HAVING COUNT(*) = 2;  -- both are present!

Your version is returning DVDs that have either English or Spanish, but not necessarily both.

score 2 · Answer 2 · answered Jun 25 '20 at 01:38

Your second query does not do what you want. It checks if the dvd has at least one of the two languages (which is different than having both).

You can check for both with exists:

SELECT name, language 
FROM DvD d
WHERE 
    EXISTS (SELECT 1 FROM Subtitles s WHERE s.dvdID = d.dvdID AND language='EN')
    AND EXISTS (SELECT 1 FROM Subtitles s WHERE s.dvdID = d.dvdID AND language='ES')

With an index on subtitles(dvdID, language), this option should be more efficient than alternative using a join and aggregation (although it is a bit longer to write).

You can also do this with an aggregate suqbuery:

SELECT name, language 
FROM DvD d
WHERE (
    SELECT COUNT(*)
    FROM Subtitles s 
    WHERE s.dvdID = d.dvdID AND language IN ('EN', 'ES')
) = 2

Where And Where SQL

2 Answers2