CODEWITHSUNDEEP
mysql
iSecretePHP
iSecretePHP

Reputation: 11

Difficulty with workings of SQL IN

My understanding is that IN is like a boolean function returning a value for each instance a row from the outside query has true returned when using IN. Therefore shouldn't a result set only have as many rows as the outer query?

Table 1:

   DNUMBER DLOCATION
---------- ---------------
         1 Houston
         4 Stafford
         5 Bellaire
         5 Houston
         5 Sugarland

Table 2:

DNAME                   DNUMBER
-------------------- ----------
Research                      5
Administration                4
Headquarters                  1

So shouldn't this query only return 3 rows?

SELECT D.dname, DL.dlocation
FROM department D, dept_locations DL
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)

Result:

DNAME                DLOCATION
-------------------- -------------
Headquarters         Houston
Administration       Houston
Research             Houston
Headquarters         Stafford
Administration       Stafford
Research             Stafford
Headquarters         Bellaire
Administration       Bellaire
Research             Bellaire
Headquarters         Houston
Administration       Houston
Research             Houston
Headquarters         Sugarland
Administration       Sugarland
Research             Sugarland

Upvotes: 1

Views: 45

Answers (3)

Joshua Carmody
Joshua Carmody

Reputation: 13730

Your understanding of IN is correct. The reason you're getting more rows is because your main query is selecting from 2 tables and there is no limitation on the second one. This query will only return 3 rows:

SELECT D.dname
FROM department D
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)

But of course you'd lose your location. By adding dept_locations to the FROM clause, you're basically cross-joining the tables, and you're not limiting the rows returned from the second table with your WHERE clause, you're only limiting the rows from department

To properly limit the rows returned using your current query format, you could do this:

SELECT D.dname, DL.dlocation
FROM department D, dept_locations DL
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)
AND DL.dnumber = D.dnumber

But the best way to get the data you want is probably to use a JOIN here:

SELECT D.dname, DL.dlocation
FROM department D
JOIN dept_locations DL on DL.dnumber = D.dnumber

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89651

SELECT D.dname, DL.dlocation
FROM department D, dept_locations DL
WHERE D.dnumber IN
(SELECT dnumber FROM dept_locations)

Has no join criteria (explicit or implicit) restricting the rows to be matched in any way, so performs a cross join (all combinations of rows on the left with rows on the right).

Upvotes: 1

David Fells
David Fells

Reputation: 6798

SELECT D.dname
     , GROUP_CONCAT(DL.dlocation)
  FROM department AS D 
 INNER 
  JOIN dept_locations AS DL 
    ON D.dnumber = DL.dnumber 
 GROUP 
    BY D.dname;

I'm making a few assumptions, but I think this will return the data you're trying to get. It'll grab each type (research, etc) alongside a comma separated list of locations.

Your query doesn't need a subquery at all, just a proper join conditional.

Upvotes: 0

Related Questions