Friday, August 19, 2016

SQL Joins

SQL Joins are used to relate information in different tables.

A Join query retrieves rows from two or more tables.

A SQL Join condition is used in the SQL 
WHERE Clause of select, update, delete statements.


 Joins in SQL

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.

Joins Example

Lets use the below two tables to explain the sql join conditions.
Database table "product";
product_idproduct_namesupplier_nameunit_price
100CameraNikon300
101TelevisionOnida100
102RefrigeratorVediocon150
103IpodApple75
104MobileNokia50
Database table "order_items";
order_idproduct_idtotal_unitscustomer
510010430Infosys
51011025Satyam
510210325Wipro
510310110TCS
SQL Joins can be classified into Equi join and Non Equi join.
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.
2) SQL Non equi joins
It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=
1) SQL Equi Joins:
An equi-join is further classified into two categories:
a) SQL Inner Join
b) SQL Outer Join 

a) SQL Inner Join:

All the rows returned by the sql query satisfy the sql join condition specified.

SQL Inner Join Example:

If you want to display the product information for each order the query will be as given below. Since you are retrieving the data from two tables, you need to identify the common column between these two tables, which is the product_id.
The query for this type of sql joins would be like,
SELECT order_id, product_name, unit_price, supplier_name, total_units 
FROM product, order_items 
WHERE order_items.product_id = product.product_id; 
The columns must be referenced by the table name in the join condition, because product_id is a column in both the tables and needs a way to be identified. This avoids ambiguity in using the columns in the SQL SELECT statement.
The number of join conditions is (n-1), if there are more than two tables joined in a query where 'n' is the number of tables involved. The rule must be true to avoid Cartesian product.
We can also use aliases to reference the column name, then the above query would be like,
SELECT o.order_id, p.product_name, p.unit_price, p.supplier_name, o.total_units 
FROM product p, order_items o 
WHERE o.product_id = p.product_id; 

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; 
The output would be like,
product_idproduct_nameorder_idtotal_units
----------------------------------------------------
100Camera
101Television510310
102Refrigerator51015
103Ipod510225
104Mobile510030
NOTE: If the (+) operator is used in the left side of the join condition it is equivalent to left outer join. If used on the right side of the join condition it is equivalent to right outer join.

SQL Self Join:

A Self Join is a type of sql join which is used to join a table to itself, particularly when the table has a FOREIGN KEY that references its own PRIMARY KEY. It is necessary to ensure that the join statement defines an alias for both copies of the table to avoid column ambiguity.
The below query is an example of a self join,
SELECT a.sales_person_id, a.name, a.manager_id, b.sales_person_id, b.name
FROM sales_person a, sales_person b 
WHERE a.manager_id = b.sales_person_id;

2) SQL Non Equi Join:

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

SQL Non Equi Join Example:

If you want to find the names of students who are not studying either Economics, the sql query would be like, (lets use student_details table defined earlier.)
SELECT first_name, last_name, subject 
FROM student_details 
WHERE subject != 'Economics' 
The output would be something like,
first_namelast_namesubject
---------------------------------------
AnajaliBhagwatMaths
ShekarGowdaMaths
RahulSharmaScience
StephenFlemingScience

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.

The constraints available in SQL are Foreign Key, Not Null, Unique, Check.
Constraints can be defined in two ways 
1) The constraints can be specified immediately after the column definition. This is called column-level definition.

2) The constraints can be specified after all the columns are defined. This is called table-level definition. 


1) Primary key:

Defines a column or combination of columns which uniquely identifies each row in the table.
Syntax to define a Primary key at column level:
column name datatype [CONSTRAINT constraint_name] PRIMARY KEY
Syntax to define a Primary key at table level:
[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)
  • column_name1, column_name2 are the names of the columns which define the primary Key.
  • The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.
For Example: To create an employee table with Primary Key constraint, the query would be like.
Primary Key at column level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
salary number(10), 
location char(10) 
);
or
CREATE TABLE employee
( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);
Primary Key at column level:
CREATE TABLE employee 
( id number(5), 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);
Primary Key at table level:
CREATE TABLE employee 
( id number(5), NOT NULL,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)
);

2) Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key.
Syntax to define a Foreign key at column level:
[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)
Syntax to define a Foreign key at table level:
[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);
For Example:
1) Lets use the "product" table and "order_items". 

Foreign Key at column level:
CREATE TABLE product 
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, 
product_name char(20),
supplier_name char(20),
unit_price number(10)
);
CREATE TABLE order_items
( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);
Foreign Key at table level:
CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);
2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like,
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10) 
);

3) SQL Not Null Constraint :

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.
Syntax to define a Not Null constraint:
[CONSTRAINT constraint name] NOT NULL
For Example: To create a employee table with Null value, the query would be like
CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10) 
);

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.
Syntax to define a Unique key at column level:
[CONSTRAINT constraint_name] UNIQUE
Syntax to define a Unique key at table level:
[CONSTRAINT constraint_name] UNIQUE(column_name)
For Example: To create an employee table with Unique key, the query would be like,
Unique Key at column level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE 
);
or
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) CONSTRAINT loc_un UNIQUE 
);
Unique Key at table level:
CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location) 
);

5)  Check Constraint :

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.
Syntax to define a Check constraint:
[CONSTRAINT constraint_name] CHECK (condition)
For Example: In the employee table to select the gender of a person, the query would be like
Check Constraint at column level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1) CHECK (gender in ('M','F')), 
salary number(10), 
location char(10) 
); 
Check Constraint at table level:
CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1), 
salary number(10), 
location char(10), 
CONSTRAINT gender_ck CHECK (gender in ('M','F')) 
);