Tutorial on SQL Joins @ 1800-865-4183.

SQL Joins are mainly used to relate information present in different tables.

A Join statement is a kind of condition in the SQL query, capable to retrieve rows or fetch records from multiple tables. A SQL Join condition is typically used in the SQL WHERE Clause of select, update, delete statements.

Syntax of Joins in SQL

The Syntax for joining two tables in SQL is:

SELECT column1, column2, column3… FROM tablename1, tablename2 WHERE tablename1.column2 = tablename2.column1;

In case a SQL join condition is omitted or if it is invalid, then the “join” operation will lead to a Cartesian product.

The Cartesian product returns a number of rows equal to the product of all rows present in all the tables being joined.

For instance, if the first table has 30 rows and the second table has 20 rows, the result will be 30 * 20, or 600 rows. This query takes pretty long time to get executed.

Example: SQL Joins

Let’s assume the shown below two tables to understand the SQL join conditions.

Database table “ProductTable”

Database table “OrderItemsTable”

SQL Joins can be mainly categorized into Equi join and Non Equi join.

1) SQL Equi joins

This is a simple SQL join condition that uses the equal sign as the comparison operator. Two kinds of Equi joins are SQL Outer join and SQL Inner join.

For example: You can extract the information regarding a customer who purchased the products and also, the quantity of respective products.

2) SQL Non Equi joins

This is also a kind of SQL join condition that makes use of some comparison operator other than the equal sign like >, <, >=, <= 1)

SQL Equi Joins:

Further, an Equi-Join are of two types: a) SQL Inner Join b) SQL Outer Join a).

SQL Inner Join:

All the rows or records are returned by the SQL query that satisfies the specified SQL join condition.

Example: SQL Inner Join:

In case you want to display the product information for each & every order the query will be as mentioned below.

Since you are retrieving the data or record from two tables, then you require identifying the common column between these two tables, based on the Product.

The query for this type of SQL joins would be like,

SELECT OrderId, ProductName, UnitPrice, SupplierName, TotalUnits FROM ProductTable, OrderItemsTable WHERE OrderItemsTable.ProductId = ProductTable.ProductId;

The columns should be referenced by the table name present in the join condition, as ProductId is a column present in both the tables and requires a way to be identified.

This averts ambiguity in using the columns present in the SQL SELECT statement. The number of join conditions is (n-1), in case there are more than two tables joined in a query where ‘n’ is the number of tables involved. Then the rule should be true to avoid Cartesian product.

We are also allowed to use aliases to refer the column name, and then the above mentioned query would be modified as follows:

SELECT o.OrderId, p.ProductName, p.UnitPrice, p.SupplierName, o.TotalUnits FROM ProductTable p, OrderItemsTable o WHERE o.ProductId = p.ProductId;

b) SQL Outer Join:

This SQL join condition returns all the records or rows fetching from both the tables which satisfy the join condition along with rows that do not satisfy the join condition from one of the tables.

The SQL outer join operator in Oracle is (+) and this is used on one side of the join condition only.

The syntax may differ in different RDBMS implementation.

Few of them depict the join conditions as “SQL left outer join”, “SQL right outer join”.

In case you want to display all the product data or records along with order items data, having null values displayed for order items, and in case a product has no order item, then the SQL query for outer join would be as follows:

SELECT p.ProductId, p.ProductName, o.OrderId, o.TotalUnits FROM OrderItemsTable o, ProductTable p WHERE o.ProductId (+) = p.ProductId;

The result would be as follows :

KINDLY NOTE:

In case the (+) operator is used on the left side of the join condition it is equivalent to left outer join. In case it is used on the right side of the join condition it is equivalent to right outer join.

SQL Self Join:

A Self Join is a kind of SQL join that is used to join a table to it, specifically when the table has a FOREIGN KEY that references its own PRIMARY KEY.

It is essential to ensure that the join statement defines an alias for both replicas of the table to avoid column ambiguity.

The query given below is an example of a self join,

SELECT a.SalesPersonId, a.Name, a.ManagerId, b.SalesPersonId, b.Name FROM Sales Person a, Sales Person b WHERE a.ManagerId = b.SalesPersonId;

2) SQL Non Equi Join:

This is a SQL Join whose condition is established via using all comparison operators except the equal (=) operator. Like >=, <=, <, >

SQL Non Equi Join Example: Call Now: 1800-865-4183.

In case you wish to find the names of students who are not studying either Economics, then the SQL query would be as follows, (lets use Student Details Table defined earlier.)

SELECT FirstName, LastName, Subject FROM StudentDetails WHERE Subject! = ‘Economics’

The output would be as shown below:

Source: AskforAccounting.com