SQL Joins

SQL Joins
    
Join:

SQL Join is used to fetch data from two or more tables,which is joined to appear as single set of data.SQL join 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 tables.Minimum required condition for joininig table is (n-1) where n,is the number of tables,A table can also join to it self is known as, self join.
The SQL Syntax for joining two tables is:

SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;

      If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Different types of Joins:

1) SQL Equi joins

It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.
For example: You can get the information about a customer who purchased a product and the quantity of product.
  
a)SQL Inner Join:
   
The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.

Syntax:
The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
  
b) SQL Outer Join:
       
This sql join condition returns all rows from both tables which satisfy the join condition along with rows which do not satisfy the join condition from one of the tables. The sql outer join operator in Oracle is ( + ) and is used on one side of the join condition only.
The syntax differs for different RDBMS implementation. Few of them represent the join conditions as "sql left outer join", "sql right outer join".
If you want to display all the product data along with order items data, with null values displayed for order items if a product has no order item, the sql query for outer join would be as shown below:

SELECT p.product_id, p.product_name, o.order_id, o.total_units
FROM order_items o, product p
WHERE o.product_id (+) = p.product_id;

2) SQL Non equi joins
 
It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <= .

3)LEFT JOIN:
 
The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.
This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

Syntax:
The basic syntax of LEFT JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

4)RIGHT JOIN:

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.
This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

Syntax:
The basic syntax of RIGHT JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

5)CARTESIAN  JOIN:

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:
The basic syntax of INNER JOIN is as follows:

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

Sub queries:

     In SQL Server, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.
Use subqueries for the following purposes:
•    To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement
•    To define the set of rows to be included in a view or materialized view in a CREATE VIEW or CREATE MATERIALIZED VIEW statement
•    To define one or more values to be assigned to existing rows in an UPDATE statement
•    To provide values for conditions in a WHERE clause, HAVING clause, or START WITH clause of SELECT, UPDATE, and DELETE statements
•    To define a table to be operated on by a containing query

You do this by placing the subquery in the FROM clause of the containing query as you would a table name. You may use subqueries in place of tables in this way as well in INSERT, UPDATE, and DELETE statements.

•    In SQL Server (Transact-SQL), a subquery is also called an INNER QUERY or INNER SELECT.
•    In SQL Server (Transact-SQL), the main query that contains the subquery is also called the OUTER QUERY or OUTER SELECT.

WHERE clause
   Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:
SELECT p.product_id, p.product_name
FROM products p
WHERE p.product_id IN
   (SELECT inv.product_id
    FROM inventory inv
    WHERE inv.quantity > 10);
The subquery portion of the SELECT statement above is:
(SELECT inv.product_id
 FROM inventory inv
 WHERE inv.quantity > 10);
This subquery allows you to find all product_id values from the inventory table that have a quantity greater than 10. The subquery is then used to filter the results from the main query using the IN condition.
This subquery could have alternatively been written as an INNER join as follows:
SELECT p.product_id, p.product_name
FROM products p
INNER JOIN inventory inv
ON p.product_id = inv.product_id
WHERE inv.quantity > 10;
This INNER JOIN would run more efficiently than the original subquery. It is important to note, though, that not all subqueries can be rewritten using joins.
FROM clause
A subquery can also be found in the FROM clause. These are called inline views.
For example:
SELECT suppliers.supplier_name, subquery1.total_amt
FROM suppliers,
 (SELECT supplier_id, SUM(orders.amount) AS total_amt
  FROM orders
  GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;
In this example, we've created a subquery in the FROM clause as follows:
(SELECT supplier_id, SUM(orders.amount) AS total_amt
 FROM orders
 GROUP BY supplier_id) subquery1
This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.
SELECT clause
          A subquery can also be found in the SELECT clause. These are generally used when you wish to retrieve a calculation using an aggregate function such as the SUM, COUNT, MIN, or MAX function, but you do not want the aggregate function to apply to the main query.
For example:
SELECT e1.last_name, e1.first_name,
  (SELECT MAX(salary)
   FROM employees e2
   WHERE e1.employee_id = e2.employee_id) subquery2
FROM employees e1;
In this example, we've created a subquery in the SELECT clause as follows:
(SELECT MAX(salary)
 FROM employees e2
 WHERE e1.employee_id = e2.employee_id) subquery2
The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.
Nested Subquery :

    If a Subquery contains another subquery, then the subquery inside another subquery is called nested subquery.

Let us suppose we have another table called “StudentCourse” which contains the information, which student is connected to which Course. The structure of the table is:-

create table StudentCourse( StudentCourseid int identity(1,1), Studentid int, Courseid int)

When your sub query returns more than one value, then we can use some special operators for the comparison. These special operators are as listed below –

1. IN / NOT IN – This operator takes the output of inner query after inner query gets executed which can be zero or more values and send it to outer query. The outer query then fetches all the matching [IN operator] or not non matching [NOT IN operator] rows.

2. ANY – [>ANY or <ANY] – The >ANY operator takes the list of values produced by inner query and fetches all the values which are greater than the minimum value of the list. The <ANY operator takes the list of values produced by the inner query and fetches all the rows which are less than the maximum value of the list.
•    For example -: >ANY(100,200,300), the ANY operator will fetch all the values greater than 100.
•    For example -: <ANY(100,200,300), the ANY operator will fetch all the values lesser than 300.
3. ALL – [>ALL or <ALL] – The >ALL operator takes the list of values produced by inner query and fetches all the values which are greater than the maximum of the list. The <ALL operator takes the list of values produced by the inner query and fetches all the rows which are less than the minimum value of the list.
•    For example -: >ALL(100,200,300), the ALL operator will fetch all the values greater than 300.
•    For example -: <ALL(100,200,300), the ALL operator will fetch all the values lesser than 100.
4. EXISTS – The EXISTS keyword produces a Boolean value [TRUE/FALSE]. This EXISTS checks the existence of the rows returned by the sub query.

Correlated Subquery :

    If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

Suppose we want to get the details of the Courses (including the name of their course admin) from the Course table, we can use the following query:-

    select Coursename ,Courseadminid,(select Firstname+' '+Lastname  from student where studentid=Course.courseadminid)as CourseAdminName from course.

0 comments:

Post a Comment