Updated on 12 Jul 2019 | 4 Min Read
By
 

How To Use Joins in MySQL

The SQL language is still the "lingua franca" to access all types of data. Even the NoSQL databases always end up offering some kind of SQL interface (or at least "SQL-like") to their semi-structured data to try to reach a wider audience.

The Join is undoubtedly one of the most critical operations in SQL but also one of the most difficult to understand. Let's explain each of them in a little more detail, so that do well in MySQL Interview Questions.

That is why it is still vital for any computer (in its broadest sense) to learn SQL. There is a website to learn SQL online (created by Oracle itself). Today we bring you a classic that is worth keeping in your history: how to use all types of joins in SQL. The Join instruction, as the name implies, is used to link the data of two related tables through some common field (typically a foreign key) and thus result in rows that mix data from the two (or more) tables on which we have made the Join.

Types of join in MySQL

  • Inner Join
  • Left outer Join
  • Right outer Join
  • Full outer join

1. Inner Join

The ‘complete’ Join. It is the default join that applies when we do not indicate otherwise when making the query. It returns only matched rows, the rows where the value of the field in table A that is used to make the join matches the value of the corresponding field in table B. Example: The query returns all products for which there is at least one order (the inner join will link the product field in the Order table with the primary key of that product in the Product table).

Syntax :

Select column_name(s) From table1 INNER JOIN table2 ON table1.column_name = table2.column_name;

Left outer Join

When you want all the rows for which there is a match but also those from Table A that do not match. Following the previous example, if you're going to list all the products with data of your orders but also showing those products for which you do not have an order yet, the solution would be to make a Left Outer join between Product and Order.

Syntax :

Select column_name(s) From table1 LEFT JOIN table2 ON table1.column_name = table2.column_name ;

Note: You can visit our Advanced MySQL Interview Questions and Answers that will help you to crack your interviews.

Right outer Join

It is precisely the same thing, but vice versa, when you want to list the rows of table B even if they are not related to any row in table A. It is a bit redundant operation since you could simply change the order of the tables in the Join and use a left outer to achieve the same effect. However, and as part of multiple Joins, it is useful to have both for a better understanding of the query.

Syntax :

Select column_name(s) From table1 RIGHT JOIN table2 ON table1.column_name = table2.column_name ;

Full outer join

It's like the sum of the previous two. We want both the rows of A and B, whether there is a match or not (obviously when there is a match the query will return all the fields of A and B that we have indicated, otherwise the query will return only the fields of A or B).

Syntax :

Select column_name(s) From table1 FULL OUTER JOIN table2 ON table1.column_name = table2.column_name Where condition ;

The smart reader will have realized what three cases are missing in the previous list. For me, more than different types of Join "primitives" are already combinations of operators that can be expressed with joins or with other concatenations of SQL operators. For example, the Left Join with B. key is null, and I would have written it using the ‘NOT IN’, if I want to return all rows of A that are NOT referenced in table B. It seems to me a more natural way of expressing.

And if you still cannot remember this theory, maybe this image (given below) will stay more engraved in your memory and help you stay ahead of the pack in Best Interview Questions: