How does SELECT from two tables separated by a comma work? (SELECT * FROM T1, T2)

sql

join

performance

best-practices

byAnton Shumikhin·Feb 11, 2025

Running SELECT * FROM T1, T2 performs a CROSS JOIN, uniting every row of T1 with every row from T2. In this marriage of data, the offspring (or result set) is a Cartesian product of both parents: rows in T1 times the rows in T2. Tiny tables T1 of 3 rows and T2 of 2 rows will give you a cute set of 6 rows.

SELECT * FROM T1 CROSS JOIN T2; -- That's a spacious party!

Good to remember: these parties can bloat your output and without a limiting WHERE clause can make your SQL hangover very unpleasant.

Exploring the comma in the FROM clause

The innocent SELECT * FROM T1, T2 holds deeper truths. It's a secret handshake SQL-ers use for an implicit CROSS JOIN, giving you every possible row combination of both tables. Beware of using this with massive datasets, it could be like opening Pandora's box of unexpected results and performance issues.

Situations to avoid CROSS JOINs

Knowing when to use a CROSS JOIN is crucial! Here are some avoidances:

Titanic tables: Many rows in each table means a gigantic result set, that'll sink your runtime.
Mismatched Matches: If the tables have no relation, your resulting data pairs might become an unrelated, chaotic mess.
Efficiency: Your server's resources aren't infinity stones. Excessive, unnecessary data processing could steal away your performance.

Alternative routes

Commonly, a more restricted JOIN, such as INNER JOIN, LEFT JOIN, or RIGHT JOIN with an ON clause, which will match recipes based on a common ingredient, is a wiser choice:

-- INNER JOIN retrieves matching pizzas 
SELECT * FROM T1
INNER JOIN T2 ON T1.key = T2.key; -- It's like magic!

-- LEFT JOIN includes all pizzas from T1 and matching pizzas from T2
SELECT * FROM T1
LEFT JOIN T2 ON T1.key = T2.key; -- No pizza left behind

Visualization

Think of two tables as a menu in a restaurant:

🍔 Table T1: [Burger, Fries, Soda]
🍕 Table T2: [Pizza, Wings, Garlic Knots]

SELECT * FROM T1, T2 is like you want all combos, and you're a big eater!

👨‍🍳 Your Order:
- [🍔&🍕]
- [🍔&🍗]
- [🍔&🍜]
- [🍟&🍕]
- [🍟&🍗]
- [🍟&🍜]
- [🥤&🍕]
- [🥤&🍗]
- [🥤&🍜]

Every possible pair, like infinite food challenge!

Decoding the SQL CROSS JOIN

Mastering the CROSS JOIN is a vital SQL skill as it:

Predicts Output: Grasping the multiplying effect of a CROSS JOIN allows you to forecast the size of your result set.
Optimizes Query Execution: Armed with this knowledge, you can write more efficient joins.
Aids Troubleshooting: Stumbling upon an unexpected number of rows in your output? You might be unintentionally using a CROSS JOIN!

Efficient execution strategies

Query execution can be demanding with CROSS JOINS. To minimize resource usage:

Use Explicit Joins: Opt for INNER JOIN, LEFT JOIN, and RIGHT JOIN using specific ON conditions like a recipe for success.
Indexing: Index your joint columns for faster search and match.
Filter: Use a WHERE clause to limit rows where conditions apply.