SQL query to get orders without customers

Question

There are two tables:

customers(id int PRIMARY KEY, firstname , lastname, address)

and

orders(id, product_name, product_price, date_order, id_customer int FOREIGN KEY, amount)

I need to get first and last names of all customers table and appropriate product_names from orders table, also show customers without orders and orders without customers (if any) Sort the data by firstname, lastname and product name P.S. The SQL version does not support FULL JOIN

My query is:

SELECT customers.firstname, customers.lastname, orders.product_name
FROM customers
    LEFT JOIN orders ON customers.id = orders.id_customer
WHERE customers.id IS NULL AND orders.id_customer IS NULL
ORDER BY customers.firstname, customers.lastname, orders.product_name;

Is this query correct or can I use right join also so that I would get complex join or not ?

See [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query) ... As it seams you are asking how to simulate a `FULL [OUTER] JOIN` in MySQL in that case see [Efficient way to simulate full outer join in MySQL?](https://stackoverflow.com/questions/3447215/efficient-way-to-simulate-full-outer-join-in-mysql) — Raymond Nijland, Aug 19 '19 at 18:29
indeed also see [How do I ask and answer homework or job interview related questions?](https://meta.stackoverflow.com/questions/334822/how-do-i-ask-and-answer-homework-questions) — Raymond Nijland, Aug 19 '19 at 18:30

score 1 · Answer 1 · answered Aug 19 '19 at 19:45

It looks that you want to simulate a FULL OUTER JOIN. You can do it in this way:

SELECT customers.firstname, customers.lastname, orders.product_name
FROM customers
LEFT JOIN orders 
    ON customers.id = orders.id_customer

UNION

SELECT customers.firstname, customers.lastname, orders.product_name
FROM customers
RIGHT JOIN orders 
    ON customers.id = orders.id_customer

ORDER BY customers.firstname, customers.lastname, orders.product_name;

Use UNION ALL if you want to show duplicates. For more information go to: How to do a FULL OUTER JOIN in MySQL?

score 0 · Answer 2 · answered Aug 19 '19 at 18:27

I doubt you really have orders with no customers, but I would recommend not exists:

select o.*
from orders o
where not exists (select 1
                  from customers c
                  where c.id = o.id_customer
                 );

Similarly, if you want customers with no orders:

select c.*
from customers c
where not exists (select 1
                  from orders o
                  where c.id = o.id_customer
                 );

score 0 · Answer 3 · answered Aug 19 '19 at 18:33

Typically, a FULL OUTER is achieved by

SELECT stuff 
FROM a LEFT JOIN b ON condition 
UNION ALL 
SELECT stuff  -- Same stuff as first
FROM b LEFT JOIN a ON condition -- Same condition as first
WHERE a.some_field_that_would_not_be_null_if_there_were_a_match IS NULL
;

The first LEFT JOIN grabs everything that would have had a match, and all the a that didn't, the second is then used with a WHERE to only grab the records from b that had no a (otherwise you double the matched results).

score 0 · Answer 4 · answered Aug 19 '19 at 18:45

Here you specified that id_customber is FOREIGN KEY, so is it possible that without primary key id table's record into customer table, order table have record ?

but as per your requirement you can write query as below

SELECT customers.firstname, customers.lastname, orders.product_name
FROM customers
    LEFT JOIN orders 
        ON customers.id = orders.id_customer
WHERE orders.id_customer IS NULL
ORDER BY customers.firstname, customers.lastname, orders.product_name;

SELECT customers.firstname, customers.lastname, orders.product_name
FROM orders 
    LEFT JOIN customers
        ON customers.id = orders.id_customer
WHERE customers.id IS NULL
ORDER BY customers.firstname, customers.lastname, orders.product_name;

This appears to be multiple queries – Strawberry Aug 19 '19 at 21:04 — Strawberry, Aug 19 '19 at 21:04

score 0 · Answer 5 · answered Sep 19 '20 at 17:05

Actually, a full query to satisfy the task will be:

SELECT customers.firstname, customers.lastname, orders.product_name
FROM customers
    LEFT JOIN orders
        ON customers.id = orders.id_customer
UNION
SELECT customers.firstname, customers.lastname, orders.product_name
FROM customers
    LEFT JOIN orders
        ON customers.id = orders.id_customer
WHERE orders.id_customer IS NULL
UNION
SELECT customers.firstname, customers.lastname, orders.product_name
FROM orders
    LEFT JOIN customers
        ON customers.id = orders.id_customer
WHERE customers.id IS NULL
ORDER BY customers.firstname, customers.lastname, orders.product_name;

SQL query to get orders without customers

5 Answers5