- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
What are MySQL joins?
152 Lectures 16 hours
87 Lectures 5.5 hours
As we know that MySQL SELECT can be used to retrieve information from a single table at a time but all the questions cannot be answered using just one table. So for the above said purpose i.e. drawing on information stored in multiple tables, we can use Join operation.
Basically, MySQL join is a method of linking data from two or more tables based on the values of the common column between them, with the result being a new temporary table. In other words, we can say that a MySQL join enables us to retrieve records from two or more logically related tables in a new temporary table. The new temporary table is created based on the column(s) that the two tables share, which represents meaningful column(s) of comparison.
The common values are usually the same column name and data-type that appears in both the participating table being joined. These column(s) are called the join key or common key.
MySQL supports following types of joins:
Actually, a cross join is the basic form of a join. If we have two tables, it takes each row of table1 and appends it to each row of table2. Hence if table1 has 3 rows and table2 has 2 rows then we will get total 6 rows after cross joining these tables. To understand it, we are taking the example of two tables named tbl_1 and tbl_2 which are having following data:
mysql> Select * from tbl_1; +----+--------+ | Id | Name | +----+--------+ | 1 | Gaurav | | 2 | Rahul | | 3 | Raman | | 4 | Aarav | +----+--------+ 4 rows in set (0.00 sec) mysql> Select * from tbl_2; +----+---------+ | Id | Name | +----+---------+ | A | Aarav | | B | Mohan | | C | Jai | | D | Harshit | +----+---------+ 4 rows in set (0.00 sec)
Now, the query below will do the CROSS JOIN between the above-mentioned tables.
mysql> Select tbl_1.id, tbl_2.id FROM tbl_1 CROSS JOIN tbl_2; +----+----+ | id | id | +----+----+ | 1 | A | | 2 | A | | 3 | A | | 4 | A | | 1 | B | | 2 | B | | 3 | B | | 4 | B | | 1 | C | | 2 | C | | 3 | C | | 4 | C | | 1 | D | | 2 | D | | 3 | D | | 4 | D | +----+----+ 16 rows in set (0.00 sec)
Inner Join or Equi join
To form an inner join we need to specify a particular condition which is known as join-predicate. Actually inner or equi join need rows in the two joined tables to have matching columns values. To understand it, following query will inner join the tables named ‘tbl_1’ and ‘tbl_2’.
mysql> SELECT tbl_1.id,tbl_2.id FROM tbl_1 INNER JOIN tbl_2 ON tbl_1.name = tbl_2.name; +----+----+ | id | id | +----+----+ | 4 | A | +----+----+ 1 row in set (0.00 sec)
The result set shows that the column name have the same values for id = 4 in tbl_1 and for id = ‘A’ in tbl_2.
A join-predicate is also required for Left join as was required in inner join. Only difference is that when joining two tables using a LEFT join, the concept of left table and right table are introduced. It returns all the rows in the left table including rows that satisfy join-predicate and also rows which do not satisfy join-predicate. For the rows that do not match the join-predicate, NULL appears in the column of the right table in the result set. To understand it, following query using the table tbl_1 and tbl_2 perform the LEFT join:
mysql> SELECT tbl_1.id,tbl_2.id FROM tbl_1 LEFT JOIN tbl_2 ON tbl_1.name = tbl_2.name; +----+------+ | id | id | +----+------+ | 1 | NULL | | 2 | NULL | | 3 | NULL | | 4 | A | +----+------+ 4 rows in set (0.02 sec)
It is similar to LEFT join except that the treatment of tables is reversed. With a RIGHT join every row from the right table will appear in the result set. For the rows in the right table that do not have the matching rows in the left table, NULL appears for the columns in the left table. To understand it, following query using the table tbl_1 and tbl_2 perform the RIGHT join:
mysql> SELECT tbl_1.id,tbl_2.id -> FROM tbl_1 RIGHT JOIN tbl_2 ON tbl_1.name = tbl_2.name; +------+----+ | id | id | +------+----+ | 4 | A | | NULL | B | | NULL | C | | NULL | D | +------+----+ 4 rows in set (0.00 sec)
- What are the complexities MySQL joins involve?
- What are MySQL constraints?
- In MySQL, how can we maintain data-driven table relationship using joins?
- How to write PHP script by using MySQL JOINS inside it to join two MySQL tables?
- How can INTERSECTION between tables be implemented with the help of MySQL joins?
- How DIFFERENCES between tables can be implemented with the help of MySQL joins?
- What are MySQL group functions?
- What are MySQL database engines?
- Example and usage of JOINS in DB2
- MySQL SELECT from table A that does not exist in table B using JOINS?
- What are the MySQL Supported Platforms?
- How can we write MySQL query for inner joins with the help of Comma operator?
- How can we write MySQL query for inner joins with the help of keyword JOIN?
- How can we write MySQL query for cross joins with the help of Comma operator?