SQL Joins

In this blog we will be learned about all types of joins and its differences.

How it will join two tables and get common rows in SQL . Like these concepts will be briefly explained with syntax and with diagrams.

What is a SQL join?

A SQL Join is a special form of generating a meaningful data by combining multiple tables relate to each other using a “Key”. Typically, relational tables must be designed with an unique column and this column is used to create relationships with one or more other tables. When you need a result-set that includes related rows from multiple tables, you’ll need to use SQL join on this column.

Join clause used to combine rows from two or more tables. based on a related column between them. Using a Primary key and foreign keys which has a common relationship between two tables.

Primary key

A primary key is used to ensure that data in the specific column is unique. Primary key column should not be null. Each table should have only one primary key.

Foreign key

A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables.

Join Conditions

join queries must have atleast one condition either in the From clause or in the where clause.

join condition will compare two columns from two different tables.

Types of SQL join

1. Inner join

2. Outer join

. Left outer join

. Right outer join

. Full outer join

3. Cross join

4. Self join

Inner join

An inner join will select all rows from both tables that satisfies the join condition. value of the common field will be the same in both tables.

Syntax :

SELECT TableA.column1,TableA.column2,TableB.column1,.... FROM TableA INNER JOIN TableB ON TableA. matching_column = TableB. matching_column;

Here TableA(Left tble) and TableB (Right table)are two tables and matching_column is Common column to both tables .

Note:

We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.

Outer join

Inner join will return the Both tables matching rows. But Outer join will return all matching and unmatching rows from the other table.

Left outer join

left outer join displays all matching records of both table along with the records in left hand side table(TableA) of join clause which are not in right hand side table(TableB) of join clause.

SQL left outer join will return all the records from the left table in the join clause,

extended with nulls if they do not satisfy the join condition.

Syntax :

SELECT TableA.column1,TableA.column2,TableB.column1,.... FROM TableA LEFT JOIN TableB ON TableA. matching_column = TableB. matching_column;

Note:

We can also write LEFT JOIN instead of LEFT OUTER JOIN. LEFT JOIN is same as LEFT OUTER JOIN.

Right outer join

Right outer join is similar to left outer join.

Right outer join displays all matching records of both table along with the records in right hand side table(TableB) of join clause which are not in left hand side table(TableA) of join clause.

SQL Right outer join will return all the records from the right table in the join clause, extended with nulls if they do not satisfy the join condition.

Syntax :

SELECT TableA.column1,TableA.column2,TableB.column1,.... FROM TableA RIGHT JOIN TableB ON TableA. matching_column = TableB. matching_column;

Note:

We can also write RIGHT JOIN instead of RIGHT OUTER JOIN. RIGHT JOIN is same as RIGHT OUTER JOIN.

Full outer join

Full outer join returns all rows from both the tables left and right side of the join clause, extended with nulls if they do not satisfy the join condition.

Full outer join combines the effects of the SQL left joins and Right joins. Many databases do not support the implementation of full SQL outer joins.

Syntax :

SELECT TableA.column1,TableA.column2,TableB.column1,.... FROM TableA FULL JOIN TableB ON TableA. matching_column = TableB. matching_column;

Note:

We can also write FULL JOIN instead of FULL OUTER JOIN. FULL JOIN is same as FULL OUTER JOIN.

3. Cross join

CROSS JOIN is also known as CARTESIAN JOIN. In a CROSS JOIN there is a join for each row of one table to every row of another table. This usually happens when the matching column or WHERE condition is not specified.

A CROSS JOIN returns all rows for all possible combinations of two tables.

In the absence of a WHERE condition the CARTESIAN JOIN will behave like a CARTESIAN PRODUCT(A*B) . i.e., the number of rows in the result-set is the product of the number of rows of the two tables.

For example, if the left table has 10 rows and the right table has 10 then the cross join result will yield 100 rows.

In the presence of WHERE condition this JOIN will function like a INNER JOIN.

Cross join is similar to an inner join where the join-condition will always evaluate to True.

Syntax :

SELECT TableA.column1,TableA.column2,TableB.column1,.... FROM TableA CROSS JOIN TableB

4. Self join

SELF JOIN is a mechanism of joining a table to itself. You would use a self join when you wanted to create a result set joining records in the table with some other records from the same table.

Syntax :

SELECT A.Column_Name, B.Column_Name,….

FROM TableA T1, TableA T2

WHERE A.Common_Field = B.Common_Field;

Here, T1 and T2 are two aliases used for the same table named Table A in the database and the WHERE clause denotes any given conditional expression.

Conclusion

As seen, we use JOINs to append & get the fields from different tables.

• Inner Join fetches records where the given condition is fulfilled.

• Left Join will give you all the rows from the left table, even when the given condition does not match.

• Right Join will give you all the rows from the left table, even when the given condition does not match.

• Full Join returns all the rows when there is a match in one of the tables.

• Join queries can be used with commands like – SELECT, INSERT, UPDATE, and DELETE.

• Joins also get along with different clauses like – GROUP BY, SUB QUERIES, WHERE, AGGREGATE FUNCTIONS, etc.

Thank you For Reading my blog.