Geek asks and answers

JOIN vs. WHERE: Why are two queries that get the same results that show a performance difference of 3-4 orders of magnitude?

Earlier tonight, I asked https://stackoverflow.com/a/21294738/ about how to write an SQL query to filter rows from a table, returning only rows with duplicates in one field.

Here is a question repeated for convenience:

If I have this data:

code1 code2
  1    10       <-- Desired (1 appears more than once)
  1    11       <-- Desired (1 appears more than once)
  2    20
  3    30       <-- Desired (3 appears more than once)
  3    31       <-- Desired (3 appears more than once)
  4    40
  5    50

... And I want to write a single SQL query , the results of which are as follows:

code1 code2
  1    10       <-- This result appears because 1 appears more than once above
  1    11       <-- This result appears because 1 appears more than once above
  3    30       <-- This result appears because 3 appears more than once above
  3    31       <-- This result appears because 3 appears more than once above

(i.e. one SQL query that returns all rows for which any data in the column is code1displayed more than once) ...

How should I do it?

SQL-, .

SQL # 1:

SELECT code1, code2
FROM myTable
WHERE code1 IN 
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)

SQL # 2:

SELECT t.code1, code2
FROM myTable t
  INNER JOIN
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)
     s on s.code1 = t.code1

:

myTable ~ 30000 , 400 2 . MySQL , SQL # 1 ~ 30 , SQL # 2 .

- .

, , , , .

SQL, .

: SQL # 2 5 000 , SQL # 1 ?

+3
1

MySQL . 5.6.5 , , .

, , MySQL :

SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1

( ), myTable , , .

, , IN, :

SELECT t1.code1, t1.code2
FROM myTable t1
WHERE EXISTS
    (   SELECT t2.code1 
        FROM myTable t2
        WHERE t2.Code1 = t1.Code1
        GROUP BY t2.code1 
        HAVING COUNT(t2.code1) > 1
    )

, code myTable . , , , , , , , , .

, ~ 30 000 , ~ 30 000 400 . .

- .

+3

More articles:


All Articles