Beginners guide to MySQL and MariaDB

In MySQL JOINS are used for joining two more tables to get result as single table. The JOINS can be used with SELECT, UPDATE and DELETE statement.

Up till now we have seen selecting the data from a single table, but in many cases you would want to get data from multiple tables with one query.

MySQL joins can be very complex depending on how you are storing your data. There are multiple type of joins such as 

1. INNER JOIN
2. OUTER JOIN
3. LEFT JOIN
4. STRAIGHT JOIN etc

More details on MySQL joins can be found from this page

But let's briefly check what a JOIN can do

INNER JOIN

Let's take a looks at couple of tables below

First table is an Orders table with data below

Second table is our Customers table with data shown bleow

As you can see that Customer and Orders table has One to Many relationship, what that means is that One customer may have more than One orders, in our case Alex has 2 orders. 

If you want to get Customer Orders, this is where you would want to JOIN 2 tables.

Query

SELECT Customers.CustID, Customers.Name, Orders.OrderID, Orders,Product 
FROM  Orders INNER JOIN Customers 
ON Orders.CustID=Customers.CustID;

Above query will return the following output

Above is an example for INNER JOIN. INNER JOIN return all rows from multiple tables where the join condition is met.

LEFT JOIN

The LEFT JOIN returns all rows from the left table, In the query below, left table is Customers, with the matching rows in the right table, In our case the right table is Orders. If there is no match to right table then NULL is returned instead

Here is a our query to explain the logic

Query

SELECT Customers.CustID, Customers.Name, Orders.OrderID, Orders,Product 
FROM  Customers LEFT JOIN Orders 
ON Orders.CustID=Customers.CustID;

Above query will return the following data

This is especially helpful if you want to check in one hit if certain customer ever purchased from your store.