I have a question regarding the use of JOIN in SQL in this case:
I have a produtos table and a destaques table that contains the id of the product. I need to make a query that returns only the records that are not in the table highlights.
I tried this:
SELECT p.* FROM produto p
INNER JOIN destaques d ON p.idProduto!=d.idProduto
Only this query continues to return records that are also in the table highlights, did not work as expected.
First of all, for organization purposes we will keep the table of interest (product) on the left, and the reference table (highlights) on the right, so we can use LEFT JOIN .
The LEFT JOIN is appropriate, because we want what you have on the left, even though there is no matching, and we do not care what you have in the right one without matching.
Next, let's condition the return where the right data 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 can not break the JOIN link, 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 WHERE . And the desired condition is when there is no match, so null:
WHERE ISNULL(d.idProduto);
Understanding the right% s for each situation is important. I suggest reading this: