Can someone explain this query

Question

here is the query

SELECT * FROM customers  
WHERE 
     NOT EXISTS 
     (     
          SELECT 1 FROM brochure_requests     
          WHERE  brochure_requests.first_name = customers.customer_first_name AND    
          brochure_requests.last_name = customers.customer_last_name
     )

This query works just fine but I am not sure why it works. In the NOT EXISTS part SELECT 1 what is the 1 for. When I ran this query

select 1 from test2

Here were the results:

1
-----
1
1
1
1
1
1
1
1
1
1
1
..

How does the not exists query work?

Answer 1

The compiler is smart enough to ignore the actual SELECT in an EXISTS. So, basically, if it WOULD return rows because the filters match, that is all it cares about...the SELECT portion of the EXISTS never executes. It only uses the EXISTS clauses for evaluation purposes

I had this misconception for quite some time since you will see this SELECT 1 a lot. But, I have seen 42, *, etc....It never actually cares about the result, only that there would be one :). The key to keep in mind that SQL is a compiled language, so it will optimize this appropriately.

You could put a 1/0 and it will not throw a divide-by-zero exception...thus further proving that the result set is not evaluated. This is shown in this SQLFiddle

Code from Fiddle:

CREATE TABLE test (i int)
CREATE TABLE test2 (i int)

INSERT INTO test VALUES (1)
INSERT INTO test2 VALUES (1)

SELECT i
FROM test
WHERE EXISTS
(
  SELECT 1/0
  FROM test2
  WHERE test2.i = test.i
)

And finally, more to your point, the NOT simply negates an EXISTS, saying to IGNORE any rows that match

Answer 2

The subquery is a correlated subquery joining between the customers and brochure_requests tables on the selected fields.

The EXISTS clause is simply a predicate that will only return the matching rows (and the NOT negates that).

Answer 3

The query :

select 1 from test2

shows you the value 1 as the value for all the records in test2 table.

Every SELECT query must have at least one column. I think that's why an unnamed column, which has the value 1, is used here.

The sub-query gives you the rows of the related Customers from the table brochure_requests.

NOT EXISTS causes the main query to return all the rows from the Customers table, which are not in the table brochure_requests.

Answer 4

The relational operator in question is known as 'antijoin' (alternatively 'not match' or 'semi difference'). In natural language: customers who do not match brochure_requests using the common attributes first_name and last_name.

A closely related operator is relational difference (alternatively 'minus' or 'except') e.g. in SQL

SELECT customer_last_name, customer_first_name
  FROM customers  
EXCEPT
SELECT last_name, first_name  
  FROM brochure_requests;

Answer 5

if customer requested a brochure, subquery returns 1 for this customer. and this customer not be added to return resultset. bcouse of NOT EXISTS clause.

Answer 6

Note: I don't know Oracle, and am not especially expert in SQL.

However, SELECT 1 from simply returns a 1 for every row matching the from clause. So the inner select can find a brochure_requests row whose name fields match those of the customer row currently being considered, it will produce a 1 result and fail the NOT EXISTS.

Hence the query selects all customers who do not have a brochure_request matching their name.

Answer 7

For each row of the table Customers, the query returns the rows when the sub-query. NOT EXISTS returns no row.

If the sub-query in NOT EXISTS returns rows, then the rows of the table Customers are not returned.