CODEWITHSUNDEEP
sqlwhere-in
Arthur
Arthur

Reputation: 199

"WHERE 1 IN" Clause in SQL

I am new to SQL and get confused by the "WHERE 1 IN" clause, my question is:

let's use the database from w3schools as example.

If I do query as:

SELECT C1.CustomerID From Customers C1
WHERE 1 IN 
  (SELECT COUNT(O1.CustomerID) FROM Orders O1 
   WHERE C1.CustomerID = O1.CustomerID);

What is the meaning of this query? Any help will be appreciated, thanks!

Upvotes: 2

Views: 970

Answers (3)

Pரதீப்
Pரதீப்

Reputation: 93734

A meaningful way to write query will be using exists. You are just checking the whether the count is 1 in sub-query for each customer

SELECT C1.CustomerID
FROM Customers C1
WHERE EXISTS  (SELECT 1
               FROM Orders O1 
               WHERE C1.CustomerID = O1.CustomerID
               HAVING COUNT(1)=1
              );

Upvotes: 0

David Hoelzer
David Hoelzer

Reputation: 16361

It is checking to see if the count() returned from the sub-select returns one. The reason is that you can't use count() or other aggregate functions in a where clause. Writing it in this way allows you to make the count() part of the sub-select and, hence, we can check for the count in the returned value.

An alternative approach to the IN is to use the HAVING construct to move the aggregate function into the "right hand side" of the Select.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270311

This query is saying that the count of orders for the customer is 1. It is perhaps clearer if written using =:

SELECT C1.CustomerID
FROM Customers C1
WHERE 1 = (SELECT COUNT(O1.CustomerID)
           FROM Orders O1 
           WHERE C1.CustomerID = O1.CustomerID
          );

The subquery is called a correlated subquery because the WHERE clause has columns from the outer query. So, for each customer, it is counting the number of orders that match that customer id. Only customers that have exactly one order are in the result set.

Upvotes: 4

Related Questions