A complete guide to SQL from scratch- Part-III (Joins)
In our previous blogs, we have covered the basics of SQL(Structured Query language), DDL (Data Definition Language), and DML(Data Manipulation Language).
And also discuss how to implement restrictions and modifications to the table structure.
Link to part 1 of this series- https://statusneo.com/sql/
Link to partt2 of this series- https://statusneo.com/sql-constraints/
In this blog, we are going to cover the different types of joins.
What are SQL Joins?
SQL joins are the operations performed to fetch the data from two or more tables.
Different types of SQL Joins are-
- Inner Join (Also known as simple join)
- Cross Join
- Natural Join
- Left Outer Join (Also known as left join)
- Right Outer Join (Also known as right join)
- Full Outer Join (Also known as full join)
Let’s discuss each one of them.
Inner Join (Simple Join)
Inner Join operation returns the matching values in both the table, that is the intersection of the table on the applied condition.
SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
Cross Join operation returns the cartesian product of the two tables. It doesn’t let us specify the join clause, but we can use the where clause in it. It combines each row of one table to the other.
Select * from table1 CROSS JOIN table2;
The Natural Join operation returns the rows from the two tables based on the common columns. Common columns are the ones having the same name in both the table and they must be of the same data type. Every row from both tables will appear in this case. The difference between natural join and inner join is that in natural join the common column will appear only once in the output while in inner join it will appear twice.
Select * FROM table1 NATURAL JOIN table2;
Left Outer Join
The Left Join operation returns all the rows from the LEFT-side table specified in the ON condition and only those rows from the other table where the joined fields are equal.
SELECT columns FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column;
Right Outer Join
The Right Join operation returns all the rows from the Right-side table specified in the ON condition and only those rows from the other table where the joined fields are equal.
SELECT columns FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column;
Full Outer Join
The Full Join operation returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in a place where the join condition is not met.
SELECT columns FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column;
That’s it from this blog, in our next blog we are going to cover the grouping and aggregation of data.
Visit my website to know more- Utkarsh Shukla website