Introduction
In SQL Joins with Example, SQL is the key to relational databases, with the help of which we can handle data. It provides us with features such as Triggers, Injection, and hosting; Joins are just one of the most important concepts to master in SQL.
SQL Joins with Example are mainly used when users simultaneously try to extricate data from multiple tables. The join keyword unites two or more tables and develops a temporary image of the united table. Then following the conditions provided, it extracts the required data from the image table, and once data is fetched, the temporary image of the merged tables is dumped.
What are Joins?
In SQL Joins with Example, JOINS are commands that combine rows from two or more tables based on a related column between those tables. They are predominantly used when a user tries to extract data from tables with one-to-many or many-to-many relationships.
SQL Joins are mainly used when users simultaneously try to extricate data from multiple tables. The join keyword unites two or more tables and develops a temporary image of the united table. Then following the conditions provided, it extracts the required data from the image table, and once data is fetched, the temporary image of the merged tables is dumped.
Give a visit to best data science course chennai
How many types of SQL Joins?
In SQL Joins with Example, JOINS are commands that combine rows from two or more tables based on a related column between those tables. They are predominantly used when a user tries to extract data from tables with one-to-many or many-to-many relationships.
In SQL Joins with Example, SQL Joins are four types. They are Cross, Self, Inner, and Outer.
CROSS JOINS in SQL
In SQL Joins with Example; the Cross Join is the first table’s rows with all the rows of the second table. Let’s say we have c rows in the first table and d rows in the second. Then the resulting cross-join table will have c*d rows. This generally happens when the matching column or WHERE condition is not specified.
Example
Let’s assume, In SQL Joins with Example, the scenario where the first table contains employee details, i.e., employee id and employee name, and the second table contains office equipment details, i.e., equipment id and equipment name.
Table: employee details
ID | NAME |
1 
| Sneha Das |
2 | Tushar Banik |
3 | Riya Roy
|
4 | Souvik BhoumiK |
Table: office equipment
ID | Name
|
2 | laptop |
3 | phone |
Table: result 4*2=8 row
Employee ID | Employee Name | Equipment ID | Equipment Name |
1 | Sneha Das | 2 | Laptop |
1 | Sneha Das | 3 | Phone |
2 | Tushar Banik | 2 | Laptop |
2 | Tushar Banik | 3 | Phone |
3 | Riya Roy | 2 | Laptop |
3 | Riya Roy | 3 | Phone |
4 | Souvik Bhoumik | 2 | Laptop |
4 | Souvik Bhoumik | 3 | Phone |
Also, have a look at the best data science courses in india
SELF JOIN in SQL
In SQL Joins with Example, Self Join, a table is joined to itself. This means each table row is joined with itself and all other rows concerning stated conditions if any. In other words, it is a unit of two copies of the same table. This is helpful when the foreign key references the primary key of the same table.
Example
Let’s assume In SQL Joins with Example, an employee table with the following details, i.e., employee id, name, PAN Id, and supervisor id. The supervisors are there at the employee table. Therefore, the supervisor id acts like a foreign key, the primary key, as it references the employee id.
Employee ID | Name | PAN ID | Supervisor ID |
1 | Sneha Das | FGTEEXV653T8 | 4 |
2 | Tushar Banik | GHDFJH67435 | 3 |
3 | Riya Roy | JFDKRM72364 | 4 |
4 | Souvik Bhoumik | JSKDWN93473 | 7 |
Employee ID= Primary key
Supervisor ID= Foreign key
Table: Result
Supervisor |
Riya Roy |
Souvik Bhoumik |
Query
SELECT a.Name AS Supervisors
FROM Employees a, Employees b
WHERE a.ID = b.supervisor_ID;
INNER JOIN
In SQL joins with Example, Inner join returns records with matching values in both tables. So, if you perform an INNER join operation between the Employee table and the Projects table, all the tuples with matching values in both tables will be given as output.
In SQL Joins with Example, Inner SQL Join is the easiest Join where all rows from the intended tables are cached together if they meet the stated condition. Two or more tables are allowed for this Join. Inner Join can be used with SQL conditional statements like WHERE, GROUP BY, etc.
Example
Let’s assume In SQL Joins with Example, two tables of a supermarket set-up. The first table, Employee, gives us information about employees, i.e., their employee id, name, and PAN ID. Here, employeeID is the primary key that uniquely identifies each row. The second table, office equipment, gives us information about items of employee equipment, i.e., item id, employee id, item name, and quantity.
Table: Employee
Employee ID | Name | PAN ID |
1 | Sneha Das | FGTEEXV653T8 |
2 | Tushar Banik | GHDFJH67435 |
3 | Riya Roy | JFDKRM72364 |
4 | Souvik Bhoumik | JSKDWN93473 |
Table: office equipment
Item ID | Employee ID | Item Name | quantity |
1 | 2 < /td> | Laptop | 1 |
2 | 3 | Phone | 2 |
3 | 5 | Tab | 1 |
Temporary Table
Employee ID | Name | PAN ID | Item ID | Employee Shopping ID | Item NAME | Quantity |
1 | Sneha Das | FGTEEXV653T8 | nil | nil | nil | nil |
2 | Tushar Banik | GHDFJH67435 | 1 | 2 | Laptop | 1 |
3 | Riya Roy | JFDKRM72364 | 2 | 3 | Phone | 2 |
4 | Souvik Bhoumik | JSKDWN93473 | nil | nil | nil | nil |
nil | nil | nil | 3 | 5 | Tab | 1 |
Employee ID= Primary key SELECT *FROM Customers NATURAL JOIN
Employee equipment Id= Foreign key shopping_details
Table: Result
Employee ID | Employee name | PAN ID | Item ID | Item Name | Quantity |
2 | Tushar Banik | GHDFJH67435 | 2 | Laptop | 1 |
3 | Riya Roy | JFDKRM72364 | 3 | Phone | 2 |
query
SELECT *FROM Customers NATURAL JOIN shopping_details
OUTER JOINS in SQL
In SQL Joins with Example, SQL Outer joins give matched. Unmatched data rows rely on the type of outer joins. Outer joins are divided into the following types:
Left Outer Join
In SQL Joins with Example, Left outer Join, every row of the left-hand table, anyhow of following the conditions, are merged to the output table. At the same time, only even rows of the right-hand table are added.
The resulting table presents rows belonging to the left-hand table and not having values from the right-hand table as NULL values.
Suggested Blogs:-
- What is SQL | No 1 Best Information
- Triggers in SQL with Real World Examples
Example
Let’s assume In SQL Joins with Example, two tables of a supermarket set-up. The first table, Employee, gives us information about employees, i.e., their employee id, name, and email ID. Here, employeeID is the primary key that uniquely identifies each row. The second table, office equipment, gives us information about items of employee equipment, i.e., item id, employee id, item name, and quantity.
Table: Employee
Employee ID | Name | PAN ID |
1 | Sneha Das | FGTEEXV653T8 |
2 | Tushar Banik | GHDFJH67435 |
3 | Riya Roy | JFDKRM72364 |
4 | Souvik Bhoumik | JSKDWN93473 |
Table: office equipment
Item ID | Employee ID | Item Name | quantity |
1 | 2 | Laptop | 1 |
2 | 3 | Phone | 2 |
3 | 5 | Tab | 1 |
Temporary Table
Employee ID | Name | PAN ID | Item ID | Employee Shopping ID | Item NAME | Quantity |
1 | Sneha Das | FGTEEXV653T8 | nil | nil | nil | nil |
2 | Tushar Banik | GHDFJH67435 | 1 | 2 | Laptop | 1 |
3 | Riya Roy | JFDKRM72364 | 2 | 3 | Phone | 2 |
4 | Souvik Bhoumik | JSKDWN93473 | nil | nil | nil | nil |
nil | nil | nil | 3 | 5 | Tab | 1 |
Employee ID= Primary key
Employee equipment Id= Foreign key
Table: Result
Name | Item Details |
Tushar Banik | Laptop |
Riya Roy | Phone |
Sneha Das | nil |
Souvik Bhoumik | nil |
Query
SELECT Customers. Name, Shopping_Details.Item_Name
FROM Customers LEFT OUTER JOIN Shopping_Details;
ON Customers.ID = Shopping_Details
Right Outer Join
In SQL Joins with Example, the Right Outer Join, every row on the right-hand table, anyhow of following the stated conditions, are merged to the output table. When only matching rows of the left-hand table are added.
Rows depend on the right-hand table, and not having values from the left-hand table are attended as nil values in the resulting table.
Example
Let’s assume, In SQL Joins with Example, two tables of a supermarket set-up. The first table, Employee, gives us information about employees, i.e., their employee id, name, and email ID. Here, employeeID is the primary key that uniquely identifies each row. The second table, office equipment, gives us information about items of employee equipment, i.e., item id, employee id, item name, and quantity.
Table: Employee
Employee ID | Name | PAN ID |
1 | Sneha Das | FGTEEXV653T8 |
2 | Tushar Banik | GHDFJH67435 |
3 | Riya Roy | JFDKRM72364 |
4 | Souvik Bhoumik | JSKDWN93473 |
Table: office equipment
Item ID | Employee ID | Item Name | quantity |
1 | 2 | Laptop | 1 |
2 | 3 | Phone | 2 |
3 | 5 | Tab | 1 |
Temporary Table
Employee ID | Name | PAN ID | Item ID | Employee Shopping ID | Item NAME | Quantity |
1 | Sneha Das | FGTEEXV653T8 | nil | nil | nil | nil |
2 | Tushar Banik | GHDFJH67435 | 1 | 2 | Laptop | 1 |
3 | Riya Roy | JFDKRM72364 | 2 | 3 | Phone | 2 |
4 | Souvik Bhoumik | JSKDWN93473 | nil | nil | nil | nil |
nil | nil | nil | 3 | 5 | Tab | 1 |
Employee ID= Primary key
Employee equipment Id= Foreign key
Table: Result
Name | Item Details |
Tushar Banik | Laptop |
Riya Roy | Phone |
nil | Dress |
Query
SELECT Customers.Name, Shopping_Details.Item_Name
FROM Customers RIGHT OUTER JOIN Shopping_Details;
ON Customers.ID = Shopping_Details.ID;
Full Outer Join
In SQL Joins with Example, The full outer Join first merges all the rows matching the stated condition in the question and then add the last unmatched rows from both tables. We want two or more tables for the Join.
After the standard rows are added to the output table, the unmatched rows of the left-hand table are added with the following NULL values, and then odd rows of the right-hand table are added with the following NULL values.
Example
Let’s assume, In SQL Joins with Example, two tables of a supermarket set-up. The first table, Employee, gives us information about employees, i.e., their employee id, name, and email ID. Here, employeeID is the primary key that uniquely identifies each row. The second table, office equipment, gives us information about items of employee equipment, i.e., item id, employee id, item name, and quantity.
Table: Employee
Employee ID | Name | PAN ID |
1 | Sneha Das | FGTEEXV653T8 |
2 | Tushar Banik | GHDFJH67435 |
3 | Riya Roy | JFDKRM72364 |
4 | Souvik Bhoumik | JSKDWN93473 |
Table: office equipment
Item ID | Employee ID | Item Name | quantity |
1 | 2 | Laptop | 1 |
2 | 3 | Phone | 2 |
3 | 5 | Tab | 1 |
Temporary Table
Employee ID | Name | PAN ID | Item ID | Employee Shopping ID | Item NAME | Quantity |
1 | Sneha Das | FGTEEXV653T8 | nil | nil | nil | nil |
2 | Tushar Banik | GHDFJH67435 | 1 | 2 | Laptop | 1 |
3 | Riya Roy | JFDKRM72364 | 2 | 3 | Phone | 2 |
4 | Souvik Bhoumik | JSKDWN93473 | nil | nil | nil | nil |
nil | nil | nil | 3 | 5 | Tab | 1 |
Employee ID= Primary key
Employee equipment Id= Foreign key
Table: Result
Name | Item Name |
Tushar Banik | Laptop |
Riya Roy | Phone |
Sneha Das | Nil |
Souvik Bhoumik | nil |
nil | dress |
query
SELECT Customers.Name, Shopping_Details.Item_Name
FROM Customers FULL OUTER JOIN Shopping_Details
WHERE Customer.ID = Shopping_Details.ID;
Advantages of SQL
Since we have understood what SQL is all about, it’s time to know the advantages of SQL Joins with Example.
- SQL has well-defined standards
As it says, Developers of SQL have mentioned how exactly every query has to be written. There is no room for ambiguity when it comes to writing a question. The standards have to be followed.
- It is easy to learn
Yes, SQL is a language used to work with the database. Since SQL has a large user base and a well-defined standard, it is easy for a beginner to learn.
- In SQL, we can create multiple views.
This is one of the unique and early features that SQL came up with. The view is nothing but creating a virtual table. A virtual table is a temporary table for specific use. By doing this, we can protect the integrity of the data.In SQL Joins with Example, SQL can create both a single view and multiple views.
Conclusion
In SQL Joins with Example, SQL Joins are mainly used when users simultaneously try to extricate data from multiple tables. The join keyword unites two or more tables and develops a temporary image of the united table. Then following the conditions provided, it extracts the required data from the image table, and once data is fetched, the temporary image of the merged tables is dumped. Give your opinion about this on SQL Joins with Example.
Frequently Asked Questions
What are the four types of joins in SQL?
In SQL Joins with Example, there are four types of joins: left, right, inner, and outer. In general, you’ll only need to use inner and left outer joins. Which join type you use depends on whether you want to include unmatched rows in your results: If you require odd rows in the primary table, use a left outer join.
What is inner join vs left join?
The LEFT JOIN selects the standard and remaining rows from the left table. At the same time, the INNER JOIN selects only the common rows between two tables. The LEFT JOIN sets the standard and remaining rows from the left table.
How to remove duplicates in SQL?
One of the simple ways to remove duplicate data in SQL is by using the DISTINCT keyword. You can use the DISTINCT keyword in a SELECT statement to retrieve unique values from a particular column.
What is default join in SQL?
In SQL Joins with Example, this join returns records with matching values in both tables. So, if you perform an INNER join operation between the Employee table and the Projects table, all the tuples with matching values in both tables will be given as output
What is ignored in SQL?
In SQL Joins with Example, if a document doesn’t duplicate an existing record, MySQL inserts it as usual. If the document is copied, the IGNORE keyword tells MySQL to discard it silently without generating an error.
