A JOIN in MySQL helps in fetching information from several tables at once. It works by joining multiple tables when the query contains a JOIN clause. In this post, we will take a quick look at the different MySQL JOINS and their uses.
Sometimes, the use of JOIN in a query may negatively affect performance. That’s because it may lead to several table scans to fetch data that didn’t require joins. In such cases, you need query optimization in SQL Server to adjust the statement according to data requirements.
Types of Joins for Query Optimization in SQL Server
Here are the various kinds of JOINs that come into play during data fetching in SQL Server:
- INNER JOIN (or simply JOIN)
- LEFT OUTER JOIN (aka LEFT JOIN)
- RIGHT OUTER JOIN (aka RIGHT JOIN)
We will cover each of these JOINs, starting with their purpose and syntax, followed by simple examples.
1. INNER JOIN
This is one of the most commonly used joins in MySQL, Oracle, and SQL Server. In fact, nearly every DBA can recall writing at least one statement with the INNER JOIN clause when they create the index Oracle of MySQL. The result of using this type of join is a data set that contains every row from the joined tables that fulfil the condition.
How to use this JOIN: Here’s the syntax for inner joins in MySQL-
SELECT <colnames>
FROM <tabname1>
INNER JOIN <othertabname>
ON tabname1.colname = othertabname.colname;
Let us consider a quick example-
SELECT teachers.teacher_id, teachers.teacher_name, school.classroom_number
FROM schteachers
INNER JOIN schstudents
ON schteachers.classroom_number = schstudents.classroom_number;
In this INNER JOIN example, we are fetching all the names of the teachers and the classes as well as classes they teach. The query will retrieve all the students present in the classes where a teacher is teaching at a particular period.
2. LEFT OUTER JOIN/LEFT JOIN
The LEFT and RIGHT JOINS find their use less often in comparison with inner joins. The LEFT join provides a more specific set of results and doesn’t always need query optimization in SQL Server. This includes each row that fulfils the condition in the join and is present on the left side of the table. Additionally, the joined fields have to be the same.
The syntax for using this join is as follows:
SELECT <colnames>
FROM tabname1
LEFT [OUTER] JOIN tabname2
ON tabname1.colname1 = tabname2.colname1;
One example-
SELECT teachers.teacher_id, teachers.teacher_name, school.classroom_number
FROM schteachers
LEFT JOIN schstudents
ON schteachers.classroom_number = schstudents.classroom_number;
According to this instance of LEFT OUTER JOIN, the result would be every row from the teacher’s table but only those rows from the students table where the teacher’s and student’s classroom numbers match. In case there is no classroom number assigned to either of them, the fields in the schstudents table will show <null> value. You don’t necessarily need to create index Oracle in such cases.
3. RIGHT OUTER JOIN/RIGHT JOIN
This third type of join goes by either of the two names, depending on the database. It works in a manner similar to the LEFT JOIN. However, it retrieves every row from the right side of the table as the query requests in its ON condition. With these rows come only those rows from table number 2 that have values equal to their counterparts according to the join.
Look at the syntax for the RIGHT OUTER JOIN:
SELECT <colnames>
FROM tabname1
RIGHT [OUTER] JOIN tabname2
ON tabname1.colname1 = tabname2.colname1;
Given below is one of the best examples to demonstrate the use of this join:
SELECT teachers.teacher_id, teachers.teacher_name, school.classroom_number
FROM schteachers
RIGHT JOIN schstudents
ON schteachers.classroom_number = schstudents.classroom_number;
According to this query, the result set should include each row from the schteachers table. But it will only display those rows from the schstudents table whose value matches the corresponding teacher values.
Conclusion
Now that you know more about joins and the correct way to use them, make sure you use them wisely. The improper use of JOINS will result in poor database performance and the need for query optimization in SQL Server.
