Question - What are the different ways to join tables in MySQL?
Answer -
Join is used to link one or more tables together, with the common column’s values in both tables. Primarily there are four types of joins:
1. Inner Join – Inner join uses a join predicate, which is a condition used to make the join. Here is the syntax:
SELECT something FROM tablename INNER JOIN another table ON condition;
2. Left Join – Left join also requires a join condition. The left join chooses information beginning from the left table. For each entry in the left table, the left compares each entry in the right table. Here is the syntax:
SELECT something FROM tablename LEFT JOIN another table ON condition;
3. Right Join – Opposite to left join and, with one difference in the query, that is the name of join. Here care should be taken about the order of tables. Here is the syntax:
SELECT something FROM tablename LEFT JOIN another table ON condition;
4. Cross Join – Cross join has no join condition. It makes a cartesian of rows of both the tables. Here is the syntax:
SELECT something FROM tablename CROSS JOIN another table;
Note: While dealing with just one table, self-join is also possible.
It is one of the most dealt with MySQL interview questions. Interviewers do like to see if the candidate understands the basics or not and join one of the core concepts.