[Solved] SQL – Differences between two queries

by


The only difference is that the first query will return a row for each pair of rows in the table where P1.att2 > P2.att2 and P2.att1 = x, while the second query will just return one row for each row in the table where att2 is greater than in some row where att1 = x. For example, if the table contains:

att att1 att2
1   x    10
2   y    20
3   x    15
4   z    16
5   a     5
6   b    11

The first query will return

att
2
2
3
4
4
6

2 and 4 are returned twice because their att2 are greater than att2 in rows 1 and 3, but 3 and 5 are only greater than row 1. The second auery will just return:

att
2
3
4
6

If att is unique in each row, changing the first query to SELECT DISTINCT P1.att will get you the same result as in the first query. Or if there’s a primary key, you can use GROUP BY primary_key_column.

solved SQL – Differences between two queries