In MySQL, the “JOIN” clause is used to join different tables. The “ON” clause in the JOIN statement defines a join condition. While the “WHERE” clause filters the data of joining tables based on a particular condition. Users can use the “ON” and “WHERE” clauses with logical operators to integrate multiple conditions at the same time.
This post will discuss the use of “WHERE” and “ON” clauses to join tables in MySQL.
Method 1: SQL Joins Using “ON” Clause
The “ON” clause in MySQL allows you to define a condition while joining tables using the “JOIN” clause. The syntax is given below:
FROM [table1-name]
JOIN [table2-name]
ON [condition];
Example 1: Joins Using ON Clause
Let’s consider the below code that joins two tables using “ON” clause:
FROM schedule
JOIN userdata
ON schedule.day = userdata.dayoff;
The selected tables will be joined based on the “day” and “dayoff” columns that are specified with the “ON” clause.
Output
The output showed that the tables had been joined based on the particular condition.
Example 2: SQL Joins Using the “ON” Clause with Logical Operators
In MySQL, logical operators can be used with the “ON” clause to join tables based on multiple conditions. Let’s see an example:
FROM user
JOIN sample
ON user.id = sample.id AND (sample.city = 'Madrid' OR sample.Country='Mexico');
In the above code the logical operators are used within the ON clause to define the table’s joining criteria. The given tables will be joined based on the following conditions:
-
- Condition 1: Ids must be the same in both tables.
- Condition 2: In the “sample” table, either the city is “Madrid” or the country is “Mexico”.
Output
The output showed that the table “user” and “sample” have been joined according to the specified conditions.
Method 2: SQL Joins Using the “WHERE” Clause
In MySQL, the “WHERE” clause extracts the results based on certain conditions. It can be used with the “JOIN” clause to join more than one table based on a particular condition. The syntax is given below:
FROM [table1-name]
JOIN [table2-name]
WHERE [condition];
Example 1: Joining Tables Based on Specific Condition
The following is an example of joining two tables using the “WHERE” clause and the SQL “JOIN” clause:
FROM schedule
JOIN userdata
WHERE schedule.day = userdata.dayoff;
In the above code,
-
- The “JOIN” is used to join the “schedule” and “userdata” tables.
- The “WHERE” clause filters the table’s data based on the “day” and “dayoff” columns.
- The resultant table will retrieve only those records that are common in both columns of the targeted tables:
Output
The output showed that the tables had been joined according to the specified condition.
Example 2: Joining Tables Based on Multiple Conditions
The “WHERE” clause can be used with the logical operator to join tables based on multiple conditions:
FROM schedule
JOIN userdata
WHERE schedule.day = userdata.dayoff AND (schedule.ID = userdata.ID);
In the above example, multiple conditions are specified in the “WHERE” clause using the logical (AND) operator. If both conditions are evaluated as “TRUE”, then the specific record will be returned.
Output
The output demonstrates that two tables have been joined under multiple conditions (i.e., both tables’ id and day, dayoff columns had the same values).
Method 3: SQL Joins Using “ON” and “WHERE” Clauses
While joining tables in MySQL, “WHERE” and “ON” clauses can be used together. To demonstrate this, consider the following examples.
Example 1: Using a Combination of “ON” and “WHERE” Clause
This example uses the “ON” and “WHERE” clauses to join the “EMPLOYEE” and “SAMPLE” tables based on multiple conditions:
sample.City
FROM EMPLOYEE
JOIN sample ON sample.ID=EMPLOYEE.ID
WHERE EMPLOYEE.City = 'London';
In the above example:
-
- The “ON” clause is utilized for joining two tables based on their IDs.
- The “WHERE” clause filters the result according to the city column of the EMPLOYEE table.
Output
The output displayed that the tables had been joined according to the specified conditions.
Example 2: Using a Combination of “ON” and “Where” With the Logical Operator
Let’s learn how to join different tables based on multiple conditions using logical operators with the ON and WHERE clauses:
FROM user
JOIN sample
ON user.id = sample.id
WHERE sample.city = 'Madrid' OR sample.Country='Mexico';
In the above example:
-
- The “ON” clause joins the given tables based on their IDs.
- In the WHERE clause, the logical OR operator is used to filter the result according to the city or country of EMPLOYEE table.
Output
The above output showed that the tables had been joined with respect to the described conditions.
Example 3: Using Combination of ON and WHERE With the LEFT JOIN
The “LEFT JOIN” returns all the records of the left table and only the corresponding/matching records of the right table. It can be used with both “ON” and “WHERE” clauses while joining tables:
sample.City
FROM EMPLOYEE
LEFT JOIN sample ON sample.ID=EMPLOYEE.ID
WHERE EMPLOYEE.City = 'London';
Output
The output showed that the left table has all the records but the right table has only conditional matched records.
Conclusion
In MySQL, tables can be joined using the JOIN clause. A “WHERE” clause, an “ON” clause, or both “WHERE” and “ON” clauses can be used to join tables based on particular conditions. The “ON” clause in the JOIN statement defines a JOIN condition. While the “WHERE” clause filters the data of joining tables based on a certain condition. This post presented a comprehensive guide on joining tables using the “ON” or “WHERE” clauses.
