[928] SQL Tutorial
ref: Structured Query Language (SQL)
ref: SQL Self Join
ref: SQL | Functions (Aggregate and Scalar Functions)
ref: SQL | NULL functions
A generic query to retrieve from a relational database is:
SELECT [DISTINCT] Attribute_List FROM R1,R2….RM
[WHERE condition]
[GROUP BY (Attributes)[HAVING condition]]
[ORDER BY(Attributes)[DESC]];Inner Join/Simple Join
In an INNER join, it allows retrieving data from two tables with the same ID.
An Inner Join returns only the matching rows between the two tables based on a specified condition. It combines data from two tables based on a common column between them, which is specified using the ON keyword in SQL. Only the rows that meet the join condition from both tables are returned. If a row in one table does not have a matching row in the other table, that row will not be included in the result set.
Inner Join
Syntax:
SELECT COLUMN1, COLUMN2 FROM
[TABLE 1] INNER JOIN [TABLE 2]
ON Condition;
How To Use Inner Join?
Inner Join is basically performed by just selecting the records having the common values or the matching values in both tables. In case of no common values, no data is shown in the output.
Syntax:
Select Table1.Col_Name, Table2.Col_Name....
From Table1
Inner Join Table2
on Table1.Common_Col = Table2.Common_Col;If there are 3 Tables present in the database, then the Inner Join works as follows:
Select Table1.Col_Name, Table2.Col_Name, Table3.Col_Name....
From ((Table1
Inner Join Table2
on Table1.Common_Col = Table2.Common_Col)
Inner Join Table3
on Table1.Common_Col = Table3.Common_Col);Outer Join
An Outer Join returns all the rows from one table and matching rows from the other table based on a specified condition. It combines data from two tables based on a common column between them, which is also specified using the ON keyword in SQL. In addition to the matching rows, it also includes rows from one table that do not have matching rows in the other table.
Outer Join is of three types:
1. Left Outer join
Left Outer Join returns all rows of a table on the left side of the join. For the rows for which there is no matching row on the right side, the result contains NULL on the right side.
Left Outer Join returns all the rows from the left table and matching rows from the right table. If a row in the left table does not have a matching row in the right table, the result set will include NULL values for the columns in the right table.
Left Outer Join
Syntax:
SELECT T1.C1, T2.C2
FROM TABLE T1
LEFT JOIN TABLE T2
ON T1.C1= T2.C1;
2. Right Outer Join
Right Outer Join is similar to Left Outer Join (Right replaces Left everywhere). Right Outer Join returns all the rows from the right table and matching rows from the left table. If a row in the right table does not have a matching row in the left table, the result set will include NULL values for the columns in the left table.
Right Outer Join
Syntax:
SELECT T1.C1, T2.C2
FROM TABLE T1
RIGHT JOIN TABLE T2
ON T1.C1= T2.C1;
3. Full Outer Join
Full Outer Join contains the results of both the Left and Right outer joins. It is also known as cross-join. It will provide a mixture of two tables.
Full Outer Join returns all the rows from both tables, including matching and non-matching rows. If a row in one table does not have a matching row in the other table, the result set will include NULL values for the columns in the table that do not have a match.
Full Outer Join
Syntax:
SELECT * FROM T1
CROSS-JOIN T2;
SQL Self Join
Joins in SQL, a self join is a regular join that is used to join a table with itself. It basically allows us to combine the rows from the same table based on some specific conditions. It is very useful and easy to work with, and it allows us to retrieve data or information which involves comparing records within the same table.
Syntax:
SELECT columns
FROM table AS alias1
JOIN table AS alias2 ON alias1.column = alias2.column;
