SQL | Join (Inner, Left, Right and Full Joins)
In a database, a join operation combines rows from two or more tables based on a related column between them. The resulting rows contain all columns from both tables. There are several types of joins that can be used, including:
 |
| Join and types in dbms SKILLS ARENA |
INNER JOIN:
This is the most common type of join. It returns only the rows that satisfy the join condition.
Example:
SELECT * FROM users INNER JOIN orders ON users.id = orders.user_id;
This will return all rows from the orders table where there is a matching user_id in the users table.
OUTER JOIN:
This type of join returns all rows from both tables, whether or not there is a matching row in the other table. There are two types of outer joins: LEFT JOIN and RIGHT JOIN.
LEFT JOIN:
Returns all rows from the left table (users in the example below), and any matching rows from the right table (orders). If there is no match, NULL values are returned for right table's columns.
Example:
SELECT * FROM users LEFT JOIN orders ON users.id = orders.user_id;
RIGHT JOIN:
Returns all rows from the right table (orders in the example below), and any matching rows from the left table (users). If there is no match, NULL values are returned for left table's columns.
Example:
SELECT * FROM users RIGHT JOIN orders ON users.id = orders.user_id;
CROSS JOIN:
This type of join returns the Cartesian product of the two tables. In other words, it will return every possible combination of rows from the two tables.
Example:
SELECT * FROM users CROSS JOIN orders;
This will return every combination of rows from the users and orders tables. If the users table has 10 rows and the orders table has 5 rows, the result will have 50 rows (10 x 5).
Join operation is used to combine data from two or more tables in a database. There are several different types of join operations, including:
Inner Join:
An inner join returns only the rows that match in both tables. For example, if we have two tables, "Customers" and "Orders," and we want to see a list of customers who have placed orders, we could use an inner join to combine the two tables and only show the rows where there is a matching customer and order.
Left Join:
A left join returns all the rows from the left table (in our example, the "Customers" table) and any matching rows from the right table (the "Orders" table). If there is no matching row in the right table, the result will still include the row from the left table with NULL values in the right table's columns.
Right Join:
A right join is the opposite of a left join, returning all the rows from the right table (in our example, the "Orders" table) and any matching rows from the left table (the "Customers" table). If there is no matching row in the left table, the result will still include the row from the right table with NULL values in the left table's columns.
Full Outer Join:
A full outer join returns all the rows from both tables, regardless of whether there is a match in the other table. If there is no match, the result will include the row from one table with NULL values in the columns from the other table.
Example:
Customers Table:
| CustomerID | Name |
|---|---|
| 1 | John |
| 2 | Sarah |
| 3 | Mike |
Orders Table:
| OrderID | CustomerID | OrderTotal |
|---|---|---|
| 1 | 1 | $100 |
| 2 | 1 | $50 |
| 3 | 2 | $75 |
| 4 | 3 | $25 |
Inner Join:
| CustomerID | Name | OrderID | OrderTotal |
|---|---|---|---|
| 1 | John | 1 | $100 |
| 1 | John | 2 | $50 |
| 2 | Sarah | 3 | $75 |
| 3 | Mike | 4 | $25 |
Left Join:
| CustomerID | Name | OrderID | OrderTotal |
|---|---|---|---|
| 1 | John | 1 | $100 |
| 1 | John | 2 | $50 |
| 2 | Sarah | 3 | $75 |
| 3 | Mike | 4 | $25 |
| 4 | NULL | NULL | NULL |
Right Join:
| CustomerID | Name | OrderID | OrderTotal |
|---|---|---|---|
| 1 | John | 1 | $100 |
| 1 | John | 2 | $50 |
| 2 | Sarah | 3 | $75 |
| 3 | Mike | 4 | $25 |
| NULL | NULL | 5 | $100 |
Full Outer Join:
______________________________________
Please share this post and blog link with your friends.For more programs use this blog.
If you have any problem, please comment in comment box, subscribe this blog for notifications of new post on your email and follow this blog.If you have any method of this program or want to give any suggestion send email on hc78326@gmail.com
Created by-- HARSH CHAUHAN
