How to Join Tables In MySQL?

12 minutes read

To join tables in MySQL, you can use the SELECT statement along with the JOIN keyword.


The JOIN keyword allows you to combine rows from two or more tables based on a related column between them. The related column is typically a primary key in one table that matches a foreign key in another table.


There are different types of JOINs in MySQL:

  1. INNER JOIN: Returns only the matched rows from both tables based on the join condition. If there is no match, the rows are not included in the result.
  2. LEFT JOIN (or LEFT OUTER JOIN): Returns all the rows from the left table and the matched rows from the right table based on the join condition. If there is no match, NULL values are returned for the columns of the right table.
  3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all the rows from the right table and the matched rows from the left table based on the join condition. If there is no match, NULL values are returned for the columns of the left table.
  4. FULL JOIN (or FULL OUTER JOIN): Returns all the rows from both tables, including unmatched rows. If there is no match, NULL values are returned for the columns of the table where the match is missing.


To use JOINs in MySQL, you need to specify the join condition using the ON keyword followed by the column(s) to join. Here's an example of joining two tables using INNER JOIN:

1
2
3
4
SELECT table1.column1, table2.column2
FROM table1
INNER JOIN table2
ON table1.primary_key = table2.foreign_key;


In this example, replace table1 and table2 with the actual table names, and column1 and column2 with the desired columns to retrieve.


By using join operations, you can combine data from different tables to retrieve the desired information for analysis, reporting, or any other data manipulation task in MySQL.

Best MySQL Books to Read in 2024

1
Murach's MySQL (3rd Edition)

Rating is 5 out of 5

Murach's MySQL (3rd Edition)

2
High Performance MySQL: Proven Strategies for Operating at Scale

Rating is 4.9 out of 5

High Performance MySQL: Proven Strategies for Operating at Scale

3
Learning MySQL: Get a Handle on Your Data

Rating is 4.8 out of 5

Learning MySQL: Get a Handle on Your Data

4
MySQL Crash Course

Rating is 4.7 out of 5

MySQL Crash Course

5
High Performance MySQL: Optimization, Backups, and Replication

Rating is 4.6 out of 5

High Performance MySQL: Optimization, Backups, and Replication

6
Querying MySQL: Make your MySQL database analytics accessible with SQL operations, data extraction, and custom queries (English Edition)

Rating is 4.5 out of 5

Querying MySQL: Make your MySQL database analytics accessible with SQL operations, data extraction, and custom queries (English Edition)

7
Learning PHP, MySQL & JavaScript: A Step-by-Step Guide to Creating Dynamic Websites (Learning PHP, MYSQL, Javascript, CSS & HTML5)

Rating is 4.4 out of 5

Learning PHP, MySQL & JavaScript: A Step-by-Step Guide to Creating Dynamic Websites (Learning PHP, MYSQL, Javascript, CSS & HTML5)

8
MySQL 8 Cookbook: Over 150 recipes for high-performance database querying and administration

Rating is 4.3 out of 5

MySQL 8 Cookbook: Over 150 recipes for high-performance database querying and administration

9
The MySQL Workshop: A practical guide to working with data and managing databases with MySQL

Rating is 4.2 out of 5

The MySQL Workshop: A practical guide to working with data and managing databases with MySQL


How to execute a right join in MySQL?

To execute a right join in MySQL, you can use the RIGHT JOIN keyword in your query. The RIGHT JOIN keyword allows you to select records from the right table (second table specified in the query) and the matching records from the left table.


Here is the syntax for executing a right join in MySQL:

1
2
3
4
SELECT column_names
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;


Explanation of the syntax:

  1. Start the query with the SELECT keyword followed by the column names you want to select from the resulting dataset.
  2. Specify the first table from which you want to retrieve records using the FROM keyword, followed by the table name.
  3. Use the RIGHT JOIN keyword to indicate that you want to perform a right join.
  4. Specify the second table on which you want to perform the right join, using the JOIN keyword followed by the table name.
  5. Define the join condition using the ON keyword followed by the column name(s) for the join condition.


Example:


Let's consider two tables, "Customers" and "Orders", with a common column "customer_id". To perform a right join between these tables, the query would look like:

1
2
3
4
SELECT Customers.customer_id, Customers.customer_name, Orders.order_id
FROM Customers
RIGHT JOIN Orders
ON Customers.customer_id = Orders.customer_id;


This query will retrieve all the records from the "Orders" table, along with the matching records from the "Customers" table based on the join condition. If there is no match, the values for the columns from the "Customers" table will be NULL.


What is a left join in MySQL?

A LEFT JOIN in MySQL is a type of join operation used to combine result sets from two or more tables based on a related column between them. It returns all the rows from the left table and the matching rows from the right table. If there is no match found, it returns NULL values for the columns of the right table.


The syntax for a LEFT JOIN in MySQL is as follows:


SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;


In this syntax, "table1" is the left table, "table2" is the right table, and "column" is the related column between the tables. The result set will contain all the rows from "table1" and the matching rows from "table2".


How to join tables with different column names in MySQL?

To join tables with different column names in MySQL, you can use the "JOIN" keyword along with the "ON" clause to specify the columns on which the tables should be joined. Here's an example:

1
2
3
4
SELECT *
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;


Replace table1 and table2 with the names of your tables, and column_name with the actual names of the columns that you want to use for the join. By specifying the equality between the columns in the "ON" clause, you can join the tables based on those columns, even if their names are different.


You can also specify the columns you want to select from the joined tables using the SELECT statement instead of using * to select all columns:

1
2
3
4
SELECT table1.column1, table2.column2
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;


Note that in addition to the "JOIN" keyword, there are different types of joins available in MySQL such as INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN. The specific type of join you choose will depend on your requirements and the relationship between the tables you want to join.


How to perform an inner join in MySQL?

To perform an inner join in MySQL, you can use the INNER JOIN keyword followed by the name of the table you want to join with, and the ON clause to specify the join condition. Here's the general syntax:

1
2
3
4
SELECT column1, column2, ...
FROM table1
INNER JOIN table2
ON table1.column = table2.column;


Here's an example that demonstrates how to perform an inner join:


Suppose you have two tables named customers and orders, and you want to fetch the customer information along with their corresponding orders.


Table: customers

1
2
3
4
5
customer_id | customer_name
------------|---------------
1           | John Smith
2           | Jane Doe
3           | Mike Johnson


Table: orders

1
2
3
4
5
6
order_id | customer_id | order_date
---------|-------------|------------
1001     | 1           | 2021-01-01
1002     | 2           | 2021-02-02
1003     | 1           | 2021-02-04
1004     | 3           | 2021-03-05


To perform an inner join between these two tables, you can use the following query:

1
2
3
4
SELECT customers.customer_name, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;


This query will give you the following result:

1
2
3
4
5
6
customer_name | order_id | order_date
--------------|----------|-----------
John Smith    | 1001     | 2021-01-01
John Smith    | 1003     | 2021-02-04
Jane Doe      | 1002     | 2021-02-02
Mike Johnson  | 1004     | 2021-03-05


In the result, you can see that the common customer_id from both tables is used to match the records and retrieve the corresponding information.


What is a table join in MySQL?

A table join in MySQL is a way to combine rows from two or more tables based on a related column(s) between them. It allows you to retrieve data from multiple tables by specifying a common column(s) in both tables. The join operation matches rows from each table based on the specified condition and returns a new result set that combines the selected columns from all the tables involved in the join. This result set can be used for various purposes like data analysis, reporting, or querying information that spans across multiple tables.

Facebook Twitter LinkedIn Telegram

Related Posts:

To perform a join operation in PostgreSQL, you need to use the JOIN keyword in your SQL query. There are different types of joins you can utilize including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.To execute a join operation, you need to specify the ta...
To perform a join in Oracle, you can use the JOIN keyword in your SQL query. The JOIN operation is used to combine rows from two or more tables based on a related column between them.There are different types of joins in Oracle:Inner join: Returns only the mat...
In Laravel, joining tables is a common task when you need to combine data from multiple database tables in a single query. By joining tables, you can retrieve related information from different tables based on their relationships.To join tables in Laravel, you...