Implicit join

An implicit join is a way of joining tables in SQL without using the JOIN keyword explicitly. Instead, the join is expressed using the WHERE clause.

Example of an implicit join:

SELECT *
FROM customers, orders
WHERE customers.customer_id = orders.customer_id;

In this example, the tables customers and orders are joined using a condition in the WHERE clause.

In contrast, an explicit join looks like this:

SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;

Differences:

Aspect	Implicit Join	Explicit Join
Syntax	Tables separated by commas, joined via `WHERE`	Uses `JOIN` and `ON`
Readability	Less readable in complex queries	More structured and readable
Error-proneness	Higher (e.g., accidental cross joins)	Lower, as join conditions are clearer
ANSI-92 compliance	Not compliant	Fully compliant

When is an implicit join used?

It was common in older SQL code, but explicit joins are recommended today, as they are clearer, easier to maintain, and less error-prone, especially in complex queries involving multiple tables.

Created 6 Months ago