SQL-complete-tutorial

1
-- Find clients without invoices
2
SELECT *
3
FROM clients
4
WHERE client_id NOT IN(
5
	SELECT DISTINCT client_id
6
    FROM invoices
7
);
8

9
-- Rewrite the same code by using JOIN
10
SELECT *
11
FROM clients
12
LEFT JOIN invoices USING (client_id)
13
WHERE invoice_id IS NULL;
14

15
-- Find customers who have ordered lettuce (id=3)
16
-- Select customer_id, first_name, last_name
17
USE sql_store;
18

19
-- Solution 1 by using subquery
20
SELECT 
21
	customer_id, 
22
    first_name, 
23
    last_name
24
FROM customers
25
WHERE customer_id IN (
26
		SELECT DISTINCT customer_id
27
		FROM orders
28
		WHERE order_id IN ( 
29
			SELECT order_id 
30
			FROM order_items
31
			WHERE product_id = 3)
32
);
33

34
-- Solution 2 by using subquery
35
SELECT 
36
	customer_id, 
37
    first_name, 
38
    last_name
39
FROM customers
40
WHERE customer_id IN (
41
		SELECT o.customer_id
42
		FROM order_items oi
43
		JOIN orders o USING (order_id)
44
		WHERE product_id = 3
45
);
46

47
-- Solution by using join
48
SELECT DISTINCT
49
	customer_id, 
50
    first_name, 
51
    last_name
52
FROM customers c
53
JOIN orders o USING (customer_id)
54
JOIN order_items oi USING (order_id)
55
WHERE oi.product_id = 3;
56

57