How to return only records without matching in a JOIN?

Asked

Viewed 1,898 times

3

I have a question regarding the use of JOIN in SQL in this case:

I have a table produtos and a table destaques containing the id product. I need to make a query that returns only the records that are not on the table highlights.

I tried this:

SELECT p.* FROM produto p
INNER JOIN destaques d ON p.idProduto!=d.idProduto

Only this query keeps returning records that are also in the table highlights, did not work as expected.

php mysql sql join select-sql

1 answer

8


First of all, for organization purposes we will keep the table of interest (product) on the left, and the reference (highlights) on the right, so we can use a LEFT JOIN.

The LEFT JOIN is appropriate, because we want what’s on the left, even if it doesn’t match, and we don’t care what’s on the right with no match.

Then let’s condition to return where the data on the right is null.

The result is this:

SELECT
   p.*
FROM
   produto p
   LEFT JOIN destaques d ON p.idProduto = d.idProduto
WHERE
   ISNULL(d.idProduto);

Note that we cannot break the link to JOIN, which is this:

   ON p.idProduto=d.idProduto

after all, this is the condition that relates the two tables. Once related, what filters the results is the WHERE. And the desired condition is when there is no match, therefore null:

WHERE ISNULL(d.idProduto);


Understand the JOINs suitable for every situation is important. I suggest reading this:

What is the difference between INNER JOIN and OUTER JOIN?

Browser other questions tagged php mysql sql join select-sql

You are not signed in. Login or sign up in order to post.