MySQL Note: Join in MySQL

• JOIN is a clause that is used to combine rows from two or more tables based on a related column between them, such as a foreign key.
• Example:

• Assume that you already have all the data key in into the system (here for reference), let's insert another row into "transactions" table.

INSERT INTO transactions (amount, customer_id)
VALUES (1.00, NULL);
SELECT * FROM transactions;

• Now, let's add another customer into "customers" table:

INSERT INTO customers (name)
VALUES ("Julia");
SELECT * FROM customers;

• So, here, not all transactions have a customer ID, and not all customers have ever initiated a transaction. Maybe, they are registered as a customer, but they never bought anything.

• To create an inner join, if we want the transactions table to be on the left, and customers table to be on the right, you may type in the following code:

SELECT *
FROM transactions INNER JOIN customers
ON transactions.customer_id = customers.customer_id;

• In this query, the join is performed by matching the values of the "customer_id" column in both tables. It connects related data between the "transactions" table and the "customers" table based on the shared customer ID.
• The result of this query would be a table that combines the columns from both tables, including all columns from the "transactions" table and all columns from the "customers" table.
• The rows in the result set would include only the records where the customer ID exists in both tables, creating a relationship between the two based on the foreign key and primary key relationship.

• It is not necessary for us to display all column, we can select which column we want it to display, for instance:

SELECT *
FROM transactions INNER JOIN customers
ON transactions.customer_id = customers.customer_id;

• In left join, we will display everythong from the table on the left.
• Let's try this:

SELECT *
FROM transactions LEFT JOIN customers
ON transactions.customer_id = customers.customer_id;;

• In right join, we will display everything from the table on the right.
• Let's try this:

SELECT *
FROM transactions RIGHT JOIN customers
ON transactions.customer_id = customers.customer_id;;