SQL JOINS and ALIASES

Hello everyone, in this insight, I’ll show you the JOIN clause.

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.

Different types of Joins are:

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

4. FULL JOIN

5. CARTESIAN/CROSS JOIN

First of all, we need our data.
I’ll pick 2 tables from GeeksforGeeks org, [Student] and [StudentCourse] tables.

Student

StudentCourse

ALIASES

SQL aliases are used to give a table, or a column in a table, a temporary name.
An alias only exists for the duration of the query and are often used to make column or table names more readable.
Further, we’re going to give aliases to both tables [Student] and [StudentCourse].

JOINS

A join condition defines the way two tables are related in a query by:

Specifying the column from each table to be used for the join. A typical join condition specifies a foreign key from one table and its associated key in the other table, in our example I’ll be using the “ROLL_NO” column as keys in both tables.

Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

The result is a table with the information joined.

1. INNER JOIN

Inner Join

Only the key match between the tables, the rows that aren’t matching are disregarded.

So, we’re joining the Course ID from the StudentCourse table with the name and age from the Student table, using the ROLL_NO as key.

Example:

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE
FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

Using aliases

SELECT stdCrse.COURSE_ID, std.NAME, std.AGE
FROM Student std --ALIAS
INNER JOIN StudentCourse stdCrse --ALIAS
ON std.ROLL_NO = stdCrse.ROLL_NO;

Output

Student INNER JOIN StudentCourse

2. LEFT JOIN

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join. The rows for which there is no matching row on right side, the result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.

Example:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Using aliases

SELECT std.NAME, stdCrse.COURSE_ID
FROM Student std
LEFT JOIN StudentCourse stdCrse
ON stdCrse.ROLL_NO = std.ROLL_NO;

Output

Student LEFT JOIN StudentCourse

3. RIGHT JOIN

RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join. The rows for which there is no matching row on left side, the result-set will contain null.

Example:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Using aliases

SELECT std.NAME, stdCrse.COURSE_ID
FROM Student std
RIGHT JOIN StudentCourse stdCrse
ON stdCrse.ROLL_NO = std.ROLL_NO;

Output

Student RIGHT JOIN StudentCourse

4. FULL JOIN

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both the tables. The rows for which there is no matching, the result-set will contain NULL

Example:

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Using aliases

SELECT std.NAME, stdCrse.COURSE_ID
FROM Student std
FULL JOIN StudentCourse stdCrse
ON stdCrse.ROLL_NO = std.ROLL_NO;

Output

Student FULL JOIN StudentCourse

5. CARTESIAN/CROSS JOIN

The CARTESIAN JOIN is also known as CROSS JOIN. In a CARTESIAN 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.

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

• In the absence of a WHERE condition the CARTESIAN JOIN will behave like a CARTESIAN PRODUCT . i.e., the number of rows in the result-set is the product of the number of rows of the two tables.
• In the presence of WHERE condition this JOIN will function like an INNER JOIN.
• Generally speaking, Cross join is similar to an inner join where the join-condition will always evaluate to True

Example:

SELECT Student.NAME, Student.AGE, StudentCourse.COURSE_ID
FROM Student
CROSS JOIN StudentCourse;

Using aliases

SELECT std.NAME, std.AGE, stdCrse.COURSE_ID
FROM Student std
CROSS JOIN StudentCourse stdCrse;

Output

The cartesian product between Student and StudentCourse

Sources

https://www.w3schools.com/sql/sql_join.asp

https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/

https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15

SQL | Join (Cartesian Join & Self Join) - GeeksforGeeks