JOIN IN SQL AT A GLANC

SQL JOIN

Definition:

The JOIN clause is used to combine records from two or more tables in a database. This is done by combining fields from the joined tables by using values common to each table. In other words, JOINS are used to find data from two or more tables based on the relationship between certain columns in these tables.

Standard ANSII SQL specifies four types of JOINs:

• INNER
• OUTER
• LEFT
• RIGHT

In certain cases, a TABLE or VIEW can JOIN to itself in what is called a "SELF JOIN".

The join predicate in the SQL statement is what contains the rules or conditions that identify what records are to be joined. When the SQL is evaluated, the predicate conditions are tested. If the predicate conditions are true then the combined record is produced.

An INNER JOIN is the most common join operation and is generally the default join type. An inner join creates a result by combining column values of two tables (A and B) based upon the join predicate. The join query compares each row of table A with each row of table B to find all pairs of rows which satisfy the join predicate. 

The INNER JOIN example below will only return a result when there is at least one valid match in both tables. If there are rows in "Users" that do not have matches in "Sales", those rows will not be in the result set (not returned).

Inner Join Example:

SELECT Users.Last_Name, Users.First_Name, Sales.Order_Number

  FROM Users

  INNER JOIN Sales

  ON Users.U_Id=SALES.U_Id

ORDER_BY Users.Last_Name

An OUTER JOIN does not require each record in the two joined tables to have a matching record. The joined table keeps each record even if no other matching record exists. Outer joins can be classified further into "left outer joins", "right outer joins", and "full outer joins", depending on which tables the rows are retained from (left, right, or both).

The OUTER JOIN clause is a variation of the SQL JOIN clause which enables a SELECT statement to process more than one table. Use the OUTER JOIN to return results from multiple tables when there aren't any matches between the JOINed tables. The example OUTER JOIN below will select from the Users and Orders tables all of the distinct users (first and last names) and the total amount they've spent: 

SELECT Users.FirstName, Users.LastName, SUM(Orders.OrdersAmount) AS OrdersPerUser

  FROM Users JOIN Orders

  ON Users.UserID = Orders.UserID

GROUP_BY Users.FirstName, Users.LastName;

NOTE: A "cartesian product" will result if there are no matching join columns specified. This means that a row will be returned for EVERY combination of the two tables. If one table has 1,000 rows and the second table has 3,000 rows the result set will contain 3,000,000 (three million!) rows. This is probably NOT what you want. 


A SELF JOIN is a query in which a table is joined (compared) to itself. Self-joins are useful to compare values in a specific column with other values in the same column in the same table (related data that is stored in a single table). The self join can be done by using table aliases to treat one table as though it were a different table and then joining them together. Another common use for self-joins is obtaining running counts and totals in a query. 

The general rule for writing self-joins is:

• Write the query doing the selects from the same table listed twice with different aliases
• Set up the comparison pedicates or tests
• Remove any cases where a given value would be equal to itself.

This example self join selects users who have the same ZipCode (the column names in this example are 'UserName', 'Address', 'City', and 'ZipCode'):

SELECT DISTINCT c1.UserName, c1.Address, c1.City, c1.ZipCode
FROM Users AS c1, Users AS c2
  WHERE c1.ZipCode = c2.ZipCode
  AND c1.UserName <> c2.UserName
ORDER_BY c1.ZipCode, c1.UserName;

Traditional Joins
Two Table Inner Join	SELECT <column_name>, <column_name> FROM <table_name alias>, <table_name alias> WHERE <alias.column_name> = <alias.column_name>
	SELECT p.last_name, t.title_name FROM person p, title t WHERE p.title_1 = t.title_abbrev;
Three Table Inner Join	SELECT <column_name>, <column_name> FROM <table_name alias>, <table_name alias> WHERE <alias.column_name> = <alias.column_name> AND <alias.column_name> = <alias.column_name>;
	SELECT p.last_name, r.role_name FROM person p, person_role_ie i, person_role r WHERE p.person_id = i.person_id AND i.role_id = r.role_id ORDER BY p.person_id;
Left Outer Join	SELECT <column_name>, <column_name> FROM <table_name alias>, <table_name alias> WHERE <alias.column_name = <alias.column_name> AND <alias.column_name> = <alias.column_name> (+);
	SELECT p.last_name, t.title_name FROM person p, title t WHERE p.title_1 = t.title_abbrev(+);
Right Outer Join	SELECT <column_name>, <column_name> FROM <table_name alias>, <table_name alias> WHERE <alias.column_name> (+) = <alias.column_name>;
	SELECT p.last_name, t.title_name FROM person p, title t WHERE p.title_1(+) = t.title_abbrev;
Self Join	SELECT <column_name>, <column_name> FROM <table_name alias>, <table_name alias>, <table_namealias> WHERE <alias.column_name> = < alias.column_name> AND <alias.column_name> = <alias.column_name>;
	UPDATE person SET title_2 = 'PhD' WHERE person_id = 1; COMMIT; SELECT p.last_name, t1.title_name, t2.title_name FROM person p, title t1, title t2 WHERE p.title_1 = t1.title_abbrev AND p.title_2 = t2.title_abbrev;

ANSI Joins
Inner Join	SELECT <column_name>, <column_name> FROM <table_name alias> INNER JOIN <table_name alias> ON <alias.column_name> = <alias.column_name>
	SELECT p.last_name, t.title_name FROM person p INNER JOIN title t ON p.title_1 = t.title_abbrev;
Left Outer Join	SELECT <column_name>, <column_name> FROM <table_name alias> LEFT OUTER JOIN <table_name alias> ON <alias.column_name> = <alias.column_name>
	SELECT p.last_name, t.title_name FROM person p LEFT OUTER JOIN title t ON p.title_1 = t.title_abbrev;
Right Outer Join	SELECT <column_name>, <column_name> FROM <table_name alias> RIGHT OUTER JOIN <table_name alias> ON <alias.column_name> = <alias.column_name>
	SELECT p.last_name, t.title_name FROM person p RIGHT OUTER JOIN title t ON p.title_1 = t.title_abbrev;
Full Outer Join	SELECT <column_name>, <column_name> FROM <table_name alias> FULL OUTER JOIN <table_name alias> ON <alias.column_name> = <alias.column_name>
	SELECT p.last_name, t.title_name FROM person p FULL OUTER JOIN title t ON p.title_1 = t.title_abbrev;
Natural Join	SELECT <column_name>, <column_name> FROM <table_name alias> NATURAL JOIN <table_name alias>
	SELECT adult_name, child_name FROM parents NATURAL JOIN children;
Self Join	SELECT <column_name>, <column_name> FROM <table_name alias> INNER JOIN <table_name alias> ON <alias.column_name> = <alias.column_name>,    <table_name alias> INNER JOIN <table_name alias> ON <alias .column_name> = <alias.column_name>;
	SELECT p1.last_name, t1.title_name, t2.title_name FROM person p1 INNER JOIN title t1 ON p1.title_1 = t1.title_abbrev,      person p2 INNER JOIN title t2 ON p2.title_2 = t2.title_abbrev;
Alternative Syntax Joining on commonly named column in both tables	SELECT <column_name>, <column_name> FROM <table_name alias> <join_type> <table_name alias> USING (<common_column_name>);
	-- does not work SELECT s.srvr_id, s.status, i.location_code FROM servers s INNER JOIN serv_inst i USING (s.srvr_id) WHERE rownum < 11; -- does not work either SELECT s.srvr_id, s.status, i.location_code FROM servers s INNER JOIN serv_inst i USING (srvr_id) WHERE rownum < 11; -- works SELECT srvr_id, s.status, i.location_code FROM servers s INNER JOIN serv_inst i USING (srvr_id) WHERE rownum < 11;

Cartesian Join
Table And Data For Cartesian  Product (Cross-Join) Demo	CREATE TABLE cartesian ( join_column NUMBER(10)); CREATE TABLE product ( join_column NUMBER(10));
Load Demo Tables	BEGIN   FOR i in 1..1000   LOOP     INSERT INTO cartesian VALUES (i);      INSERT INTO product VALUES (i);   END LOOP;   COMMIT; END;
Inner Join	SELECT COUNT(*) FROM cartesian c, product p WHERE c.join_column = p.join_column;
Not Inner Join	SELECT COUNT(*) FROM cartesian c, product p WHERE c.join_column != p.join_column; EXPLAIN PLAN FOR SELECT COUNT(*) FROM cartesian c, product p WHERE c.join_column != p.join_column;
Cartesian (Cross-Join) Product	SELECT COUNT(*) FROM cartesian, product;
Intentional Cartesian (Cross-Join) Product	SELECT <alias.column_name>, <alias.column_name> FROM <table_name alias> CROSS JOIN <table_name alias>;
	SELECT s.srvr_id, i.location_code FROM servers s CROSS JOIN serv_inst i WHERE rownum < 1001;

-- Cheers !!!