In the previous installment of this MySQL series, I demonstrated how you can fetch data using MySQL clauses. In this part we will learn about the Joins function in MySQL, why they are used and how to use them. Let’s get started.
How Can I Link Two Tables in MySQL
MySQL Joins let you access data from multiple tables. A MySQL Join is performed whenever two or more tables are joined in an SQL statement. MySQL Joins include: the MySQL Inner Join [also known as the Simple Join], the MySQL Left Outer Join [also called the Left Join, it returns matching records from the left table], the Right Join [this returns the matching records from the right table], and the Full Join [this returns matching records from the all tables]. Using MySQL JOINs, you’ll be able to join more than two tables.
In MySQL, the Inner Join is the Default Join. On given keywords at that point, it chooses all rows from both tables, as long as there’s a coordinate between the columns in both tables.
Unlike SQL, MySQL does not consider the Outer Join as a separate Join type. To get the same results as the Outer Join, you need to join the Left Outer Join and Right Outer Join.
How Many Tables can be Joined in MySQL
According to the official documentation of MySQL 8.0, the maximum number of tables in a JOIN statement is 61. However, note that JOIN statements could require a lot of server resources as the number of tables increases. If this is the case with your query, I highly recommend breaking it into multiple queries to reduce the load on the server.
Let us start by adding a new table in our database which will contain the message along with the user ID which have sent this message, we will name it messages. The schema of our table is:
CREATE TABLE `messages` [
`id` int[11] NOT NULL,
`message` varchar[255] NOT NULL
]
We will be using the same selectdata function which we have created in our crud.php file. Now let us get started by joining these two tables. You can also fill your table so you can practice it.
Stop Wasting Time on Servers
Cloudways handle server management for you so you can focus on creating great apps and keeping your clients happy.
Inner Join
Inner Join joins table in such a way that it only shows those results which matches the condition that is given and hide others. The structure of Inner Join queries are:
SELECT column_name[s]
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
Inner Join and simple join both are same. You can also write your query like this:
SELECT column_name[s]
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
Now let us fetch the Name and the message from our database using Inner join. The query will be like this
$sql = "SELECT CONCAT[myguests.firstname,' ',myguests.lastname] AS name, myguests.email, messages.message From myguests INNER JOIN messages ON myguests.id = messages.id";
The CONCAT function is used to join two strings column in MySQL. Now open your index.php which we have created previously copy the following code in it.
| Name | Message |