Preparing for SQL questions before any tech interview is as important as preparing for data structures and algorithms. For any tech role, such as software development engineer, data scientist, database administrator, etc, SQL is an important topic to be prepared with.
There are multiple topics in MySQL that are important. The JOINs are supposed to be one of the most important topics as well. In this tutorial, we will be seeing some most important and most asked interview questions on MySQL JOINs. So, let’s get started!
Basic MySQL JOIN Questions
Here, we have divided the questions into three types- Basic, Intermediate, and Advanced.
1. What is the purpose of JOIN?
A join clause is a SQL statement used to combine records from multiple tables or get data from these tables depending on the presence of a common field (column) between them. The tables can be linked using a SELECT statement and a join condition. Records from two or more tables in a database can be retrieved and merged using the SQL JOIN clause.
For example- We have two tables- students and student_marks. The student_marks table doesn’t contain the student names. Therefore, we will join these two tables and show the students’ names with their marks.
Now, let’s write a query to join together these two tables.
SELECT s.roll_number, s.name, m.maths, m.english, m.science
FROM students s JOIN student_marks m
ON s.roll_number=m.roll_number; Code language: SQL (Structured Query Language) (sql)
2. Is the JOIN keyword required to join two or multiple tables?
Not necessarily. A subquery can also be used instead of the JOIN keyword to join two or multiple tables. Before the JOIN keyword was introduced in the SQL, subquery was the only option to join the tables.
Apart from this, using multiple table names in the select query is also used to join the tables.
3. What is the importance of the joins over other methods?
Joins keep a normalized database. Data normalization helps reduce data redundancy so that our application experiences fewer data anomalies while deleting or updating a record. The advantage of joins is that they execute faster, making them more effective.
A join query rather than one that uses a subquery will nearly always return the data more quickly. It is possible to lessen the burden on the database by using joins. For instance, you may use a single join query rather than many queries. Consequently, you may make greater use of the database’s search, filter, and other functionality.
4. What are the different types of JOINs in MySQL?
MySQL supports inner Join, left outer join, right outer join and self join. The Full outer join is not supported in MySQL. However, it does support the CROSS join.
Intermediate MySQL JOIN Questions
1. State the difference between left join and inner join.
Left join – It includes all records from the left table and the matching records from the right table. This means the output will consist of everything from the left table as well as the records from the right table that are matched with the left table.
Inner join – It combines the two tables and generates the result that contains the matching records from both tables. It only includes those that are common in the left and right table, all other rows are excluded.
2. What is a Subquery? Is it different from Joins?
Subquery or also known as a nested query or an inner query is the mechanism in which there is a query inside the outer query. It is different from the JOINs because the JOINs contain only a single select statement. However, the query which consists of the JOIN statement may contain the subquery as well.
3. Is FULL OUTER JOIN present in MySQL?
Mysql doesn’t support full outer join as of now. It only supports inner join, left join, right join and cross join. The cross-join might give similar results to the full outer join but is totally different. To get the full outer join, we need to get the union of the left join and right join of the tables.
4. What is the SELF JOIN?
The SELF JOIN is nothing but the table joined with itself. It is important to have at least one column, say column A, that acts as the primary key and one column, say column B, that includes values that may be matched with those in column A in order to accomplish a self-join. Column B’s value may be null in some rows, and Column A’s value need not match Column B’s value exactly in every row.
Advanced MySQL JOIN Questions
1. Is UNION different from JOIN? Explain How.
Yes. The UNION is different from the JOIN clause. The JOIN clause works on the columns. Which means, it combines the data from multiple tables into new columns. The JOIN statement needs a relation between two or more tables.
On the other hand, the UNION clause works on the rows. That means, instead of forming new columns, the rows are added next to the previous rows to form the result. In the UNION, the result set of two or more queries is combined and the result sets of those queries must consist of an equal number of columns having the same data type.
2. How is the cross-join different from the full-join?
The cross join is the cartesian product of the two tables. That means the result set will contain the number of rows in the first table multiplied by the number of rows in the second table. The common attribute is not needed to perform the cross-join.
On the other hand, the full join, which is also known as full outer join, combines the data from the multiple tables to form the result set based on the relation between tables. The result set also contains the NULL values if the corresponding values are not present in one of the tables.
Also, MySQL doesn’t support the FULL JOIN clause. We have to use the UNION with the left and right join to get the result equivalent to the full join.
For example –
Consider two tables in the first question.
Let’s use the cross-join and see the result.
SELECT * FROM students
CROSS JOIN
student_marks;Code language: SQL (Structured Query Language) (sql)
Now let’s write a query using the left join, right join, and union to find the full join result set.
select * from students s left join student_marks m
on s.roll_number=m.roll_number
union
select * from students s right join student_marks m
on s.roll_number=m.roll_number;Code language: SQL (Structured Query Language) (sql)
As you can see, the cross-join generates a different result set than the full outer join.
Conclusion
Today we have seen some important and most common interview questions of the MySQL join topic. Make sure you prepare with these questions and revise at least once before appearing for the interview. Good luck!