The opposite of an INNER JOIN or to obtain the records do not match

Who knows what knowledge of SQL is the JOIN. In our guides and other articles we explained how the JOIN and how you can create relationships between different tables in a database.

The most common requirement is satisfied by the instrument JOIN to find matches between two tables, in which case rescues the INNER JOIN clause which precisely locates and returns the fields that satisfy the correspondence sought within two or more tables involved in the JOIN.

Much less intuitive to the contrary is the need to identify the fields that do not have a match within the JOIN.

Let's take an example.
Suppose you want to manage a database of an e-commerce consists of only two tables:

• customers (containing the master data of the customer)
• orders (containing orders made ​​by customers)

Now suppose you want to extract two different lists of customers: 1) a list of customers who have already placed an order, 2) the list of customers who have not yet bought anything.

To satisfy the first requirement, of course, is quite simple:

 



 SELECT CUSTOMER.FIRST, CUSTOMER.LAST







 FROM customers







 INNER JOIN Orders







 ON = clienti.id ordini.id_cliente







 ORDER BY ASC CUSTOMER.LAST

 

and so far no problem.

But how can we do to reverse the result? Well ... First, do not use an INNER JOIN, but a LEFT JOIN to find a match full but not partial, where, however, results from the left table are returned anyway.
Without this we will look (using the WHERE clause) the records are missing (ie not found in the report) then identified as NULL (in the SQL NULL is defined as a kind of "special value" that identifies an absence of value ..)

Let's see the code:

 



 SELECT CUSTOMER.FIRST, CUSTOMER.LAST







 FROM customers







 LEFT JOIN Orders







 ON = clienti.id ordini.id_cliente







 WHERE IS NULL ordini.id_cliente







 ORDER BY ASC CUSTOMER.LAST

 

The result is, in fact, the list of names of our customers who have never bought anything.