Is it better to use a subquery in the FROM clause, as to minimize the cartesian product?

Question

Suppose we have 2 tables, Customer and Address, with the following fields each:

Customer
========
Id: INT
Name: VARCHAR(25)
Address_Id: INT (FK from Address Table)


Address
=======
Id: INT
Country: VARCHAR(25)
City: VARCHAR(25)
Street: VARCHAR(25)
StreetNumber: INT

Suppose I want to receive the address and user details of a user with Id: 15. Should I use a subquery that minimizes the cartesian product, like this:

SELECT *
FROM
(
    SELECT Id
    FROM User
    WHERE Id = 15
) as u,
Address a
WHERE a.Id = u.Address_Id;

or should I just use the following obvious query?

SELECT *
FROM User u, Address a
WHERE u.Id = 15 AND u.Address_Id = a.Id;

@Matthew but, unless I am mistaken when one writes: "FROM TableName1, TableName2", that is the most basic form of a join. Were you referring to something more specific (like inner joins that happen first and if you were, why not use a subquery)? — Imperator Romanorum, Aug 26 '19 at 14:44
`FROM table1, table2` results in what's considered a "full" join. When you do `WHERE table1.Id = table2.fkid` then I would consider that an inner join. Using `JOIN table2 ON table1.id = table2.fkid` is the idiomatic way of representing a join in SQL. The goal of using the `JOIN` syntax is to give enough information to the SQL interpreter to do what you want to achieve in the most efficient way possible (by conveying your intent) — Matthew, Aug 26 '19 at 14:48
@ImperatorRomanorum the most basic join is using the keyword `JOIN`. This is more readable than the `table1, table2` 25 years old — Cid, Aug 26 '19 at 14:48
@Matthew AFAIK, both syntax are equivalent in term of being efficient. See [this](https://stackoverflow.com/a/10308304/8398549) — Cid, Aug 26 '19 at 14:49
@Cid perhaps, I'm not 100% certain about the implementation of various SQL engines (postgres, mysql, mssql, oracle, db2, etc.) — Matthew, Aug 26 '19 at 14:52
it heavy depens on MySQL version/optimizer what will happen.. From MySQL 5.7 this query wil be flattened out meaning `SELECT * FROM ( SELECT Id FROM User WHERE Id = 15 ) as u, Address a WHERE a.Id = u.Address_Id;` will internal be rewriten and optimized as `SELECT * FROM User u, Address a WHERE u.Id = 15 AND u.Address_Id = a.Id;` as MySQL knows both statements will give the same results and avoids having to create a deliverd table.. Before MySQL 5.7 it would create a deliverd table and index it before joining.. — Raymond Nijland, Aug 26 '19 at 14:57

score 2 · Accepted Answer · answered Aug 26 '19 at 14:55

You don't need a subquery here. You can use

SELECT *
FROM User u
JOIN Address a ON u.Address_Id = a.Id
WHERE u.Id = 15;

or your query

SELECT *
FROM User u, Address a
WHERE u.Id = 15 AND u.Address_Id = a.Id;

In both cases the engine will apply the condition u.Id = 15 as soon as possible - Thus only read one row from the User table.

Is it better to use a subquery in the FROM clause, as to minimize the cartesian product?

1 Answers1