Download SQL Joins: Inner, Outer, Left, Right, Full and Cross Join and more Assignments Database Programming in PDF only on Docsity!
SQL JOIN
SQL Join is used to fetch data from two or more tables, which is joined to appear as single set of data. It is used for combining column from two or more tables by using values common to both tables. JOIN Keyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is (n- 1) where n , is number of tables. A table can also join to itself, which is known as, Self Join.
Types of JOIN
Following are the types of JOIN that we can use in SQL: Inner Outer Left Right
Cross JOIN or Cartesian Product
This type of JOIN returns the cartesian product of rows from the tables in Join. It will return a table which consists of records which combines each row from the first table with each row of the second table. Cross JOIN Syntax is,
SELECT column-name-list FROM table-name1 CROSS JOIN table-name2; Copy Example of Cross JOIN Following is the class table, ID NAME 1 abhi 2 adam 4 alex and the class_info table, ID Address 1 DELHI
4 alex 2 MUMBAI 1 abhi 3 CHENNAI 2 adam 3 CHENNAI 4 alex 3 CHENNAI As you can see, this join returns the cross product of all the records present in both the tables.
INNER Join or EQUI Join
This is a simple JOIN in which the result is based on matched data as per the equality condition specified in the SQL query. Inner Join Syntax is, SELECT column-name-list FROM table-name1 INNER JOIN table-name WHERE table-name1.column-name = table-name2.column- name; Copy
Example of INNER JOIN Consider a class table, ID NAME 1 abhi 2 adam 3 alex 4 anu and the class_info table, ID Address 1 DELHI 2 MUMBAI 3 CHENNAI Inner JOIN query will be,
Example of Natural JOIN Here is the class table, ID NAME 1 abhi 2 adam 3 alex 4 anu and the class_info table, ID Address 1 DELHI 2 MUMBAI 3 CHENNAI
Natural join query will be, SELECT * from class NATURAL JOIN class_info; Copy The resultset table will look like, ID NAME Address 1 abhi DELHI 2 adam MUMBAI 3 alex CHENNAI In the above example, both the tables being joined have ID column(same name and same datatype), hence the records for which value of ID matches in both the tables will be the result of Natural Join of these two tables.
OUTER JOIN
Outer Join is based on both matched and unmatched data. Outer Joins subdivide further into,
- Left Outer Join
- Right Outer Join
- Full Outer Join
ID NAME
1 abhi 2 adam 3 alex 4 anu 5 ashish and the class_info table, ID Address 1 DELHI 2 MUMBAI
3 CHENNAI
7 NOIDA
8 PANIPAT
Left Outer Join query will be, SELECT * FROM class LEFT OUTER JOIN class_info ON (class.id = class_info.id); Copy The resultset table will look like, ID NAME ID Address 1 abhi 1 DELHI 2 adam 2 MUMBAI
ON table-name1.column-name(+) = table-name2.column- name; Copy Example of Right Outer Join ID NAME 1 abhi 2 adam 3 alex 4 anu 5 ashish Once again the class table, and the class_info table, ID Address
1 DELHI
2 MUMBAI
3 CHENNAI
7 NOIDA
8 PANIPAT
Right Outer Join query will be, SELECT * FROM class RIGHT OUTER JOIN class_info ON (class.id = class_info.id); Copy The resultant table will look like, ID NAME ID Address 1 abhi 1 DELHI 2 adam 2 MUMBAI 3 alex 3 CHENNAI
1 abhi 2 adam 3 alex 4 anu 5 ashish and the class_info table, ID Address 1 DELHI 2 MUMBAI 3 CHENNAI 7 NOIDA
8 PANIPAT
Full Outer Join query will be like, SELECT * FROM class FULL OUTER JOIN class_info ON (class.id = class_info.id); Copy The resultset table will look like, ID NAME ID Address 1 abhi 1 DELHI 2 adam 2 MUMBAI 3 alex 3 CHENNAI 4 anu null null 5 ashish null null
