CODEWITHSUNDEEP
mysql
Legendary_Linux
Legendary_Linux

Reputation: 1559

MySQL Return NULL on Failed JOIN

I have two MySql Tables: contracts and customers. Customers may have contracts linked to them. Some contracts exist without customers, and some customers have no contracts. I need to query for all customers and contracts, and where possible, join them in the same row. In rows where no contract or customer are found, I would like to simply place NULL.

As it stands, I am only returning rows where both are present. I realize that's what a join is for, but I'm not sure how to simply return everything. Specifying no JOIN clause returns each result multiple times, which also won't do.

Here's the insufficient SQL I have so far:

SELECT cu.ID, cu.`Name`, cu.Contract, co.ID, co.`Type`
FROM test.customers cu
INNER JOIN test.contracts co ON co.ID = cu.Contract;

Any help would be greatly appreciated.

Upvotes: 0

Views: 92

Answers (1)

useSticks
useSticks

Reputation: 904

What you are interested in is called a "left join". 

SELECT cu.ID, cu.`Name`, cu.Contract, co.ID, co.`Type`
FROM test.customers cu
LEFT JOIN test.contracts co ON co.ID = cu.Contract;

This will return nulls for the contracts where there are no matches.

However, that will not find instances of "no customers" as well.

The solution is to add a second query and union that in as well.

UNION 
SELECT cu.ID, cu.`Name`, cu.Contract, co.ID, co.`Type`
FROM test.contracts co
LEFT JOIN test.customers cu ON co.ID = cu.Contract
WHERE cu.ID is null;

Upvotes: 2

Related Questions