Joins in SQL

Joins in SQL with Example

by

What are Joins in SQL?

Joins in SQL combine rows from two or more tables. It creates a set of rows in a temporary table.

Joining two or more tables is one of the most powerful features of relational systems.

In fact, it is the availability of the Join operation, almost more than anything else that distinguishes relational from non-relational systems. So what is a Joins in SQL? With the help of a join, we can retrieve data from more than one table.

If we required data from more than one table then the join condition is used. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, that is, usually primary and foreign key columns

so there are two styles for writing joins syntax in SQL or Oracle first one is the oldest and the second one is a new style.

Theta Syntax (Theta style) for Join in SQL

In theta style, we use the (,) symbol to join two tables and Write the join condition in the WHERE clause.

 SELECT table1.column, table2. column2 FROM table1, table2 WHERE table. Column1 = table2. column2;

ANSI Syntax (ANSI style) for Join in SQL

 In ANSI style we write the name of join to join two tables and Write the join condition in the ON clause.

 SELECT table1.column, table2. column2 FROM table1 INNER JOIN table2 ON table1. Column1 = table2. column2;

Types of joins in SQL
 Types of joins in SQL

 Types of joins in SQL

 There are many types of joins, some main joins in SQL are given below

  1. Equijoin
  2. Non-equijoin
  3. Inner Join
  4. Natural Join
  5. Self-join
  6. Outer join
  7. Cross join

1. Equi joins- The join clause is used to combine tables based on a common column and a join condition. An equijoin is a type of join that combines tables based on matching values in specified columns. In equi join we use the = symbol in the join condition.

Point to be remembered:

  • The column names do not need to be the same.
  • The table contains repeated columns.
  • It is possible to perform an equi join on more than two tables.

There are two ways to use equi join in SQL:

SELECT * FROM TableName1, TableName2

WHERE TableName1.ColumnName = TableName2.ColumnName;

— OR

SELECT * FROM TableName1 JOIN TableName2

ON TableName1.ColumnName = TableName2.ColumnName;

Example-

Typically the tables are joined to get meaningful data. Suppose we want to get the department name of all employees along with their names.

Now since the employee name is in the employee table and the dept name is present in the dept table we have to take the join of two tables on the basis of the common column between these two tables i.e deptno column.

SELECT ename, dname, sal FROM emp,dept WHERE emp.deptno = dept.deptno;

2. Non-equijoin – When the comparison operator used in joining columns is other than equality, the join is called a Non-Equi-join. The SQL NON-EQUI JOIN uses a comparison operator instead of the equal sign like >, <, >=, <= along with conditions. Sometimes non-equijoin is also known as theta join

Example- Suppose we want to find the grades of employees based on their salary

select ename, grade,sal from emp,salgrade where sal between losal and hisal;

3. Inner Join- INNER Join is the most frequently used JOIN. It matches the records from both tables based on the join predicate and returns only the matched rows.

For ease of understanding one can think that first a Cartesian product is created and then all the rows that do not meet the join condition are dropped from the result. Inner join also has a shorthand syntax given its wide use. Let us understand this join using tables in the Employee database:

Example– SELECT ID, ENAME, E.COMPID AS E_COMPID, C.COMPID AS C_COMPID, MODEL FROM Employee E INNER JOIN Computer C ON E.COMPID = C.COMPID

4. Natural Join- We have already learned that an EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables and an equal sign (=) is used as a comparison operator in the where clause to refer to equality. The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name as associate tables will appear once only.

SQL Natural Join Syntax:- Select * FROM table1 NATURAL JOIN table2;

 SQL Natural Join example:- SELECT * FROM foods NATURAL JOIN company

5. Self-join- A self-join is a join in which a table is joined with itself (which is also called Unary relationships), especially when the table has a FOREIGN KEY that references its own PRIMARY KEY.

To join a table itself means that each row of the table is combined with itself and with every other row of the table. The self-join can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were.

SQL Self Join Syntax :- SELECT a.column_name, b.column_name… FROM table1 a, table1 b WHERE a.common_filed = b.common_field;

SQL Self Join example :- SELECT a.emp_id as “Emp_ID”, a.emp_name AS “Employee Name”, b.emp_id AS “Supervisor ID”, b.emp_name AS “Supervisor Name” FROM employee a, employee b WHERE a.emp_supv = b.emp_id;

6. Outer join – An OUTER JOIN doesn’t require each record in the two join tables to have a matching record. In this type of join, the table retains each record even if no other matching record exists.

Three types of Outer Joins are:

  1. Left Outer Join
  2. Right Outer Join
  3. Full Outer Join
  1. Left Outer Join– LEFT JOIN returns all the rows from the table on the left even if no matching rows have been found in the table on the right. When no matching record is found in the table on the right, NULL is returned.

SQL Left JoinSyntax :- Select * FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;

SQL Left JoinExample :- SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM company LEFT OUTER JOIN foods ON company.company_id = foods.company_id;

  1. Right Outer Join– The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which are matching in both the tables and the unmatched rows will also be available from the table written after the JOIN clause.

SQL Right JoinSyntax :- Select * FROM table1<br> RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name;

SQL Right JoinExample :- SELECT company.company_id,company.company_name, company.company_city,foods.company_id,foods.item_name FROM company RIGHT OUTER JOIN foods ON company.company_id = foods.company_id;

  1. Full Outer Join-  In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

SQL Full Outer JoinSyntax :- Select * FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

SQL Full Outer JoinExample :- SELECT a.company_id AS “a.ComID”, a.company_name AS “C_Name”, b.company_id AS “b.ComID”, b.item_name AS “I_Name” FROM company a FULL OUTER JOIN foods b ON a.company_id = b.company_id;

7. Cross join- The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if the nowhere clause is used along with CROSS JOIN. This kind of result is called a Cartesian Product. If the WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mention the table names involved, after a FROM clause.

SQL Cross JoinSyntax :- Select * FROM table1 CROSS JOIN table2;

SQL Cross JoinExample :- SELECT foods.item_name,foods.item_unit.

For other interesting technical blog Articles click here.

For other interesting DBMS, Articles click here.

if you have any questions regarding the database management system then comment your questions

Thank you

Leave a Comment