CODEWITHSUNDEEP
mysqlleft-join
Peter
Peter

Reputation: 1931

Mysql left join to check record using the where clause in only one table

Am trying to check if the user viewing the current product has already bought the product before by checking if the product.pid exist in table order.opid or not. The below query work without adding AND o.buyer = 'peter' in where clause, but is very important that i validate the query against the user currently viewing the product.

Products

pid   | pname 
------|--------------
100   | Toyota
200   | Lexus

order

oid  | opid  | buyer  | opname  | color
-----|-------|------------------|-------
M10  | 100   | peter  |Toyota   | red
M11  | 100   | peter  |Toyota   | black
M12  | 100   | john   |Toyota   | black
M13  | 200   | peter  |Lexus    | black

SQL QUERY 

SELECT * FROM Products p
LEFT JOIN order o
ON p.pid = o.opid
WHERE p.pid = 100
AND o.buyer = 'peter'

Upvotes: 1

Views: 43

Answers (2)

Naruto
Naruto

Reputation: 4329

I don't see anything wrong with the query.

I ran the above query

SELECT * FROM Products p
LEFT JOIN order o
ON p.pid = o.opid
WHERE p.pid = 100
AND o.buyer = 'peter'

and it is resulting in response only when buyer is peter.

But its not an optimized query as here you are fetching first all results even when data is not present in order table with null as buyer and other info and then with where clause optimizing it to remove the one where buyer is not peter. Instead, 

SELECT * FROM Products p  INNER JOIN order1 o ON p.pid = o.opid WHERE p.pid = 1 AND o.buyer = 'peter'

INNER JOIN returns all intersection data of two tables. and then where clause filters where buyer is peter

I would suggest one omtimization though in your query, don't use buyer name instead create a mapping table for user and store userId as there will be many users with same name. Its better to normalize the table.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

The condition on the second table needs to go in the on clause:

SELECT *
FROM Products p LEFT JOIN
     order o
     ON p.pid = o.opid AND o.buyer = 'peter'
WHERE p.pid = 100;

This may seem rather arcane, but the rule is easy to learn. A left join keeps all rows in the first table, regardless of whether the ON clause evaluates to true, false, or NULL. However, the values from the second table become NULL, so the WHERE clause filters out the unmatched buyers, turning the outer join into an inner join.

The same reasoning explains why conditions on the first table should go in the WHERE clause and not the ON clause.

Upvotes: 0

Related Questions