Oracle Mapping Product Against Join
At my company we use an Oracle database. I noticed that everyone writes their requests like this:
SELECT p.name, p.id, o.issued_date
FROM orders o, products p
WHERE o.productid = p.id;
What does the database do in such a situation? Doing a Cartesian product and then selecting only certain rows? It doesn't sound like that.
source to share
What does the database do in such a situation?
Same as when defining ANSI join:
SELECT *
FROM orders o
JOIN products p ON o.productid = p.id
I noticed that everyone writes their requests as follows [...]
It looks like a lot of people in your company have gained many years of experience at Oracle! I bet they also use the notation (+)
for outside unions. This was the only syntax supported by Oracle prior to version 9i .
source to share
This is actually the oldest ANSI standard for internal joining. People in other answers have mentioned that this is not ANSI syntax, but it is not entirely correct. Build as
SELECT p.name, p.id, o.issued_date
FROM orders o, products p
WHERE o.productid = p.id;
adheres to the ANSI 88 SQL standard (now called implicit union). However, it has been superseded by the newer ANSI 92 form of inner, outer and cross-join:
-- Inner join
SELECT p.name, p.id, o.issued_date
FROM orders o [INNER] JOIN products p
ON o.productid = p.id
WHERE (residual conditions);
-- Outer joins:
SELECT p.name, p.id, o.issued_date
FROM orders o LEFT|RIGHT|FULL [OUTER] JOIN products p
ON o.productid = p.id
WHERE (residual conditions);
-- Explicit corss join:
SELECT p.name, p.id, o.issued_date
FROM orders o CROSS JOIN products p
WHERE (residual conditions);
This is precisely because the previous syntax is misleading and error prone due to the fact that the join condition mixes easily with residual conditions and can be mistakenly lost. This is why it is highly recommended to use the explicit syntax.
Square brackets [] indicate "noise" (optional) keywords, vertical bar | indicates "OR" (you can put LEFT, RIGHT or FULL there, not all). Hope this helps.
source to share
That's why:
SELECT
*
FROM
Orders o
INNER JOIN
Products p
ON
p.id=o.ProductID
Note that you should never SELECT *...
specify the columns you want . You must also explicitly specify the join type (inner, left, right, outer). This is the correct way to implement join and is ANSI syntax.
source to share