[카테고리:] query

difference on “ON” and “WHERE” when OUTER JOIN

  1. WHERE  -> SELECT only the rows satisfy the WHERE condition
  2. ON -> JOIN only the rows satisfy the ON condition

-> Therefore when you OUTER JOIN,

SELECT * FROM A RIGHT JOIN B ON  A.Product_index = B.index_ WHERE B.otherIndex = 1227616;
# results only rows with otherIndex = 1227616

#same with
SELECT * FROM A INNER JOIN B ON A.Product_index = B.index_ AND B.otherIndex = 1227616;

SELECT * FROM A RIGHT JOIN B ON A.Product_index = B.index_ AND B.otherIndex = 1227616 WHERE B.otherIndex = 1227616;

-----------------------------------------------------------------
#different with
SELECT * FROM A RIGHT JOIN B ON A.Product_index = B.index_ AND B.otherIndex = 1227616;
# results all rows of B because it's RIGHT JOIN
# join only the rows with otherIndex = 1227616