fbpx
How to Join Two Tables in MySQL
July 14, 2022
join two tables in MySQL

To give appropriate content for a WordPress website, MySQL stores data in tables. Irrespective of the prefix, each MySQL database table comprises columns and rows. The columns identify the data type, whereas the rows carry the actual data.

In this article, we will learn about joins used in merging tables. Joins are used not only for two tables only, but you can join multiple tables through the same technique.  

What is SQL JOIN

You can use a JOIN clause to combine rows from two or more tables based on a related column. (W3schools).

To understand the MySQL join example, Let’s have a look at the below “orders” table:

Join Two Tables in MySQL

Now, look at the “Customers” table:

Join Two Tables in MySQL

Notice that the “CustomerID” column in the “Orders” table refers to the “CustomerID” in the “Customers” table. The relationship between the two tables above is the “CustomerID” column.

Then, you can create the following SQL query (containing an INNER JOIN), which picks records that have matching values in both tables

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate

FROM Orders

INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

and it will produce something like this:

Join MySQL

How Many Tables Can You Join in MySQL?

If you go with the MySQL 8.0 documentation, you will find that the maximum number of tables in a JOIN statement can be 61. However, don’t forget that JOIN statements can demand several server resources with the increase in tables. If you are also facing the same problem with your query, breaking the tables into multiple queries is recommended to minimize the server load.

What is Inner Join

Inner Join links tables so that it only displays those results that satisfy the condition that is given (and hides others). The structure of Inner Join queries is:

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name=table2.column_name;

There is no difference between the Inner Join and simple join. It is possible to write your query like the following:

SELECT column_name(s)

FROM table1

JOIN table2

ON table1.column_name=table2.column_name;

Read Also: PostgreSQL vs MySQL: Performance and Features

What is RIGHT JOIN

RIGHT JOIN links the two tables in a way that it returns every value from the right and matched value from the left tables. Also, it returns null on the left table when no match is found. The structure for RIGHT JOIN is:

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name=table2.column_name;

What is LEFT JOIN

LEFT Joins links two tables in a way that it returns all the values from the left and matched values from the right tables. It also returns null on the right table when no match is found. The structure for LEFT JOIN is:

SELECT column_name(s)

FROM table1

LEFT JOIN table2

ON table1.column_name=table2.column_name;

What is UNION

UNION in MySQL is used to union multiple columns from different tables into a single column. The structure of the UNION query for selecting unique values is:

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

For choosing repeated values from columns is:

SELECT column_name(s) FROM table1

Union All

SELECT column_name(s) FROM table2;

FULL JOIN

The whole outer join keyword returns all records when there is a match in either left (table1) or right (table2) table records.

Note: Return of extensive result sets is possible through FULL OUTER JOIN.

How You can Join two MySQL Tables

You can access data from multiple tables using MySQL joins. You can perform MySQL joins whenever two or more tables are linked in a MySQL statement. MySQL Joins contain

  • The MySQL Inner Join (another name is the Simple Join),
  • The MySQL Left Outer Join (another name is the Left Join, it returns matching records from the left table),
  • The Right Join (it returns the matching records from the right table), and
  • The Full Join (it returns matching records from all tables).

Through MySQL JOINs, you can join more than two tables.

The Inner Join is the Default Join in MySQL. On provided keywords, the inner join chooses every row from both tables, as long as a ‘coordinate’ between the columns (in both tables) is present.

Contrary to SQL, MySQL does not follow the Outer Join as a separate Join. To receive the same results as the Outer Join, the programmer needs to connect the Left Outer Join and Right Outer Join.

Frequently Asked Questions (FAQs)

Q. How do I join two tables together?

Solution: You can join two tables in SQL in four ways: Inner Join, Left Join, Right Join (All records in the right table and matching records in the left table), and Union (erases duplicates).

Q. What SQL command to use to join two tables?

Solution: You can join two tables using the INNER JOIN statement that returns matching records from both tables.

Last Word

In this article, we learned about MySQL joins that developers use in relational databases. Joins are used not only for two tables, but you can join more than two tables using the same technique. 

Asma Khalid is a Product Manager at CloudPages. She also oversees Content Writing and Social Media at CloudPages.

Recent Posts

Get Newsletter Updates

10 Most Common WordPress Errors and How to Fix Them

10 Most Common WordPress Errors and How to Fix Them

‘Oops! WordPress dashboard does not work correctly due to unforeseen errors.’ Your smooth and happy day can quickly turn into confusion when you open your WordPress and see this message appear on your screen. It can be pretty frustrating, especially if it is your...

read more
MySQL Performance Tuning Tips For Better Database Optimization

MySQL Performance Tuning Tips For Better Database Optimization

Though MySQL is considered one of the most popular database managers, it still needs to be optimised every once in a while. On the other hand, when we talk about complex and big data sets, you need to optimise for high performance regularly. MySQL performance tuning...

read more
How to Create Image and File Upload in PHP with jQuery AJAX

How to Create Image and File Upload in PHP with jQuery AJAX

The hallmark of the PHP application is that it enables you to upload files from clients to servers. However, you need to be patient to implement features with stress-free configuration and correct security. As a developer, you can use different scripts for PHP file...

read more
A Comprehensive Guide to WooCommerce Shortcodes

A Comprehensive Guide to WooCommerce Shortcodes

Ever heard of the term WooCommerce shortcodes? If not, don’t worry as you are not the only one on the bus. Interestingly, many people are unaware of WooCommerce WP eCommerce short codes, even those who have owned and run their WooCommerce store for a long time....

read more

Comments

0 Comments

Submit a Comment

Your email address will not be published.

Subscribe

Join Our Newsletter

Pin It on Pinterest

Share This