MYSQL JOINS
- Digital Engineering
MYSQL JOINS
JOINS :-
JOINS are used to retrieve data from multiple tables in a single query.
For JOINs to work, the tables need to be related to each other with a common key value.
MySQL Joins
Joins is to connect something in general sense, the same goes with joins in MySQL. When we do a join in MySQL, what we are doing is in essence, joining two tables together so that you can select data from both tables. This is possible when a column from the first table also contains a column with the same name in the second table.
MySQL supports the following JOIN types: INNER JOIN, OUTER JOIN (further divided into LEFT JOIN and RIGHT JOIN), SELF JOIN, and CROSS JOIN. The type of the JOIN defines how tables are related in a query. You can join more than two tables.
Let’ s see real life example of joins:-
Suppose you have two tables customers and orders, to create them the MySQL script will be something like this:
For Orders table–
Syntax—
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE `orders` ( `id` int primary key, `customer_id` int DEFAULT NULL, `amount` decimal(10,2) DEFAULT NULL ) INSERT INTO `orders` (`id`, `customer_id`, `amount`) VALUES (1, 1001, '50.00'), (2, 1002, '75.00'), (3, 1003, '20.00'), (4, 1001, '30.00'); |
The table will look like this:-
To create Customers table–
Syntax—
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE `customers` ( `customer_id` int primary key, `customer_name` varchar(50) DEFAULT NULL ) INSERT INTO `customers` (`customer_id`, `customer_name`) VALUES (1001, 'John Smith'), (1002, 'Jane Doe'), (1003, 'Bob Johnson'); |
The tables will look like :-
Let’s see how to apply inner join in it and the result.
Inner Join
INNER JOIN is a type of join in MySQL that returns only the rows that have matching values in both tables being joined. This means that only the rows that satisfy the join condition are returned.
Syntax—
|
1 2 3 4 |
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column; |
Here, columns represent the columns that you want to select from the tables, table1 and table2 represent the names of the tables that you want to join, ON is used to specify the join condition, and column represents the columns from both tables that are being used to join the tables.
|
1 2 3 |
SELECT * FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id; |
This is an example of inner join.
LEFT JOIN
LEFT JOIN is generally used to return all the rows from the left (or first) table and the matching rows from the right (or second) table, based on a specified join condition. If there is no matching row in the right table, the result set will contain NULL values for the columns of the right table.
The main use case for LEFT JOIN is when you want to include all the rows from one table, even if there is no matching row in the other table. This is particularly useful when you are working with tables that have a one-to-many relationship.
Syntax–
|
1 2 3 |
SELECT * FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id; |
This query will return all orders from the orders table, along with the corresponding customer name from the customers table. If an order does not have a matching customer record in the customers table, the customer name column will be NULL.
RIGHT JOIN
We use a RIGHT JOIN to retrieve all rows from the right table (the second table listed in the query), along with any matching rows from the left table (the first table listed in the query).
In other words, a RIGHT JOIN ensures that all rows from the right table are included in the result set, even if there are no matching rows in the left table. If there are matching rows in the left table, the RIGHT JOIN will join them to the corresponding rows in the right table, based on the join condition specified in the query.
Syntax–
|
1 2 3 |
SELECT o.id, c.customer_name, o.amount FROM orders o RIGHT JOIN customers c ON o.customer_id = c.customer_id; |
SELF JOIN
A self join is a type of join in SQL where a table is joined with itself. In other words, it’s a way of treating a single table as if it were two separate tables, and then joining them based on a common column.
Self joins are implemented in SQL by giving the table being joined an alias, which is a temporary name used to refer to the table within the context of the join. By joining the table to itself using the alias, you can compare or combine data from different rows within the same table.
Syntax–
|
1 2 3 4 |
SELECT o1.id, o1.customer_id, o1.amount, o2.amount AS previous_order_amount FROM orders o1 LEFT JOIN orders o2 ON o1.customer_id = o2.customer_id AND o1.id > o2.id ORDER BY o1.customer_id, o1.id; |
CROSS JOIN
A CROSS JOIN, also known as a Cartesian product, is a type of join in SQL that returns all possible combinations of rows from two tables. Unlike other types of joins such as INNER JOIN, LEFT JOIN, and RIGHT JOIN, a CROSS JOIN does not require a join condition or predicate to be specified.
In a CROSS JOIN, each row in the first table is combined with every row in the second table, resulting in a result set that contains all possible combinations of rows from the two tables. This can be useful in certain situations, such as when you need to generate a list of all possible combinations of items, or when you need to perform calculations based on every combination of data points.
Syntax–
|
1 2 3 |
SELECT o.id, c.customer_name, o.amount FROM orders o CROSS JOIN customers c; |
