CODEWITHSUNDEEP
mysqlsql
Anshuman_Jha
Anshuman_Jha

Reputation: 49

The meaning of (n) in a SQL query?

SELECT COURSENAME
FROM COURSE C1
WHERE (1) IN
(SELECT COUNT(DISTINCT(C2.COURSEFEES))
FROM COURSE C2
WHERE C2.COURSEFEES > C1.COURSEFEES);

What is the meaning of this 'WHERE (1) IN'?

Upvotes: 0

Views: 78

Answers (3)

Richa Monga
Richa Monga

Reputation: 71

Here WHERE (1) is comparing to resultant count from the inner query.

If below inner query returns one 1 then outer query will fetch output else no output.

(SELECT COUNT(DISTINCT(C2.COURSEFEES))
FROM COURSE C2
WHERE C2.COURSEFEES > C1.COURSEFEES)

Upvotes: 0

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

Parentheses in (1) are superfluous, so it could only be 1 IN ....

1 IN X essentially is the same as X = 1.

Since the query which replaces X returns a count, it checks if the count returned by the correlated subquery equals 1

This query is equivalent, but rewritten into X = 1 instead of 1 IN X:

SELECT COURSENAME
FROM COURSE C1
WHERE (
  SELECT COUNT(DISTINCT(C2.COURSEFEES))
  FROM COURSE C2
  WHERE C2.COURSEFEES > C1.COURSEFEES
) = 1;

It compares every course fee with other fees to return only these, that have only one larger fee than the one we're comparing at the moment, thus 

count(distinct(..)) = 1 for c2.coursefees > c1.coursefees

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270643

This is an awkward way of writing:

WHERE (SELECT COUNT(DISTINCT C2.COURSEFEES)
       FROM COURSE C2
       WHERE C2.COURSEFEES > C1.COURSEFEES
      ) = 1

This is saying that there is one course fee larger than the one in the record -- or alternatively that the course fee is the second largest.

It might be simpler to see this as:

WHERE C1.COURSEFEES = (SELECT C2.COURSEFEES
                       FROM COURSE C2
                       GROUP BY C2.COURSEFEES
                       ORDER BY C2.COURSEFEES DESC
                       LIMIT 1 OFFSET 1
                      )

Upvotes: 5

Related Questions