Can We Join Two Tables Without Using JOIN Keyword?

Yes, you can absolutely join two tables without explicitly using the JOIN keyword in SQL. This is most commonly achieved by listing multiple tables in the FROM clause and specifying the join condition in the WHERE clause.

Understanding Implicit Joins

When you list multiple tables in the FROM clause separated by commas, the database initially performs a Cartesian product (also known as a CROSS JOIN). This means every row from the first table is combined with every row from the second table, creating a result set with all possible combinations.

The WHERE clause then acts as a filter, allowing you to specify the conditions under which rows from these combined results should be included. By defining the relationship between the tables (e.g., TableA.ID = TableB.TableAID), you effectively filter the Cartesian product down to the rows that match your desired join, creating an implicit join.

How It Works:

Cartesian Product: FROM TableA, TableB generates a temporary table containing every possible pairing of rows from TableA and TableB.
Filtering: The WHERE clause then filters this large result set, keeping only the rows where the specified join condition is met.

Practical Example

Let's illustrate this with a common scenario involving Orders and Customers tables.

Example Tables:

Imagine you have two tables:

Customers: CustomerID, CustomerName, Email
Orders: OrderID, CustomerID, OrderDate, TotalAmount

Explicit JOIN (for comparison):

SELECT
    C.CustomerName,
    O.OrderID,
    O.OrderDate
FROM
    Customers C
JOIN
    Orders O ON C.CustomerID = O.CustomerID;

Implicit JOIN (without JOIN keyword):

SELECT
    C.CustomerName,
    O.OrderID,
    O.OrderDate
FROM
    Customers C,
    Orders O
WHERE
    C.CustomerID = O.CustomerID;

Both queries will produce the same result: a list of customer names alongside their order details. The key difference lies in the syntax and how the join condition is specified.

Limitations and Best Practices

While using the WHERE clause for joining is technically possible, it's important to understand its limitations and the reasons why explicit JOIN syntax is generally preferred in modern SQL.

Key Considerations:

Inner Joins Only: The WHERE clause method is primarily used to create inner joins. It cannot natively perform LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN operations, which require the explicit JOIN syntax along with ON or USING clauses. The WHERE clause can be used to create a join between tables without using the keyword JOIN, but it can only be used for inner joins.
Readability: Explicit JOIN syntax (INNER JOIN, LEFT JOIN, etc.) is often considered more readable and maintainable, especially for complex queries involving multiple joins. It clearly separates the join conditions from the filtering conditions.
Clarity: Developers can immediately understand the type of join being performed when JOIN keywords are used, improving code comprehension and reducing potential errors.
ANSI Standard: Explicit JOIN syntax is part of the ANSI SQL standard, promoting consistent query writing across different database systems.

When to Use (and Not Use)

While the implicit join method exists and works for inner joins, it is generally considered a legacy syntax.

Use for: Understanding historical SQL or working with older codebases that might still use this convention.
Avoid for: New development. Always prefer explicit JOIN syntax (INNER JOIN, LEFT JOIN, etc.) for clarity, readability, and the ability to perform various types of joins.

For more in-depth information on different SQL join types and best practices, you can explore resources like W3Schools SQL JOIN or SQLJoins.com.