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.
Table of Contents
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:
Now, look at the “Customers” table:
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:
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.
