SQL Commands:

SQL commands are instructions, coded into SQL statements, which are used to
communicate with the database to perform specific tasks, work, functions and
queries with data.

SQL commands can be used not only for searching the database but also to perform
various other functions like, for example, you can create tables, add data to tables, or
modify data, drop the table, set permissions for users. SQL commands are grouped
into four major categories depending on their functionality:

• Data Definition Language (DDL) - These SQL commands are used for
creating, modifying, and dropping the structure of database objects. The
commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

• Data Manipulation Language (DML) - These SQL commands are used for
storing, retrieving, modifying, and deleting data.
These Data Manipulation Language commands are: SELECT, INSERT, UPDATE,
and DELETE.

• Transaction Control Language (TCL) - These SQL commands are used for
managing changes affecting the data. These commands are COMMIT,
ROLLBACK, and SAVEPOINT.

• Data Control Language (DCL) - These SQL commands are used for providing
security to database objects. These commands are GRANT and REVOKE.

SQL SELECT Command : Introduction

• The SQL SELECT command is the only Data Query Language Command over
which whole data extraction and retrieval is dependent.

• The SQL SELECT command is used for “fetching and extracting data from
databases or tables”.

• While fetching data through SELECT command, certain conditions can be attached
in order to generate specific results from the query. Let’s look at the examples now:

SQL/ Sumati/BCA/Trident 1
SQL SELECT Command : Syntax

• There are a number of possible combinations while using SELECT query out of
which some are listed below.

column
SQL SELECT Command : Syntax

OR

SQL/ Sumati/BCA/Trident 2
SQL SELECT Command : Example

• We will continue with the database i.e db_school and table i.e student_details we
had created earlier and try to give a possible example for each of the above
mentioned syntax available. Considering the table and details we had:

• Table : student_details

Query-1 : SELECT * from student_details;

Output:

SQL/ Sumati/BCA/Trident 3
 Query-2 : SELECT name from student_details;

Output:

Query-3 : SELECT name, marks from student_details;

Output:

Query-4 : SELECT * from student_details where marks = 50;

Output:

OPERATORS

The Arithmetic operators supported are:

Operators Description

+ Addition

– Subtraction

* Multiplication

/ Division

SQL/ Sumati/BCA/Trident 4
Comparison Operators

• The comparison operators are used in such conditions that compare one expression to
another.

• The format of the operator in the WHERE clause is WHERE Expression OPERATOR
VALUE.

• The different Comparison Operators are given below:

Symbol Name

= Equality Operator

<>, !=, ^= Not Equality Operator

> Greater Than Operator

< Less Than Operator

>= Greater Than or Equal to Operator

<= Less Than or Equal to Operator

Examples of Comparison Operators:

Query:

SELECT Ename, Sal, Job FROM Emp WHERE Job='MANAGER';

Output:

Query:

SELECT Ename, Sal, Job FROM Emp WHERE Sal>=3000;

SQL/ Sumati/BCA/Trident 5
Output:

Logical Operators

Logical Operators combine the results of two-component conditions to produce a single result.
Logical operators provided by ORACLE are:

Symbol Name

AND Logical Conjunction Operator

OR Logical Disjunction Operator

NOT Logical Negation Operator

AND Operator

• It returns TRUE if both or all component conditions are TRUE.

• It returns FALSE if either is FALSE.

OR Operator

• It returns TRUE if either of the components is TRUE.

• It returns FALSE if both or all component conditions are FALSE

NOT Operator

• It returns TRUE if the following condition is FALSE.

• It returns FALSE if the following condition is TRUE.

BETWEEN … AND … Operator

• It is used to display rows based on a range of values.

• The declared range is inclusive.

SQL/ Sumati/BCA/Trident 6
 • The lower limit should be declared first.

• The operator can be used upon any data type.

• The negation of the operator is NOT BETWEEN …AND….

Query:

SELECT Ename, Sal, Job FROM Emp WHERE sal BETWEEN 1000 AND 6000;

Output:

IN Operator

• It is used to test for values in a specified list.

• The operator can be used upon any data type.

• The negation of this operator is NOT IN.

Query:

SELECT * FROM EMP WHERE ENAME IN ('SMITH', 'WARD');

IS NULL Operator

• This operator tests for NULL values.

• It is the only operator that can be used to test for NULL.

• The negation of this operator is IS NOT NULL.

Query:

SELECT Ename, Sal, Comm FROM Emp WHERE Comm IS NULL;

Output:

SQL/ Sumati/BCA/Trident 7
Query:

SELECT Ename, Sal, Comm FROM Emp WHERE Comm IS NOT NULL;

Output:

LIKE Operator

The LIKE operator is used in character string comparisons with pattern matching.

With the LIKE operator, you can compare a value to a pattern rather than to a constant.

Pattern can be in one of the following:-

Wildcard Explanation

% Used for matching string

_ Used for matching single character

Example 1

Query: select * from table1 where name like 's%'

SQL/ Sumati/BCA/Trident 8
Example 3

Query: select * from table1 where name NOT like 's%'

SQL INSERT Command : Introduction

• Once the tables in the databases are created, it’s now time to insert values into these
tables. This can be done using SQL INSERT command. INSERT command can be
used in two different types. Both of them are explained below. Let’s look at its
syntax first :

SQL/ Sumati/BCA/Trident 9
SQL INSERT Command : Syntax

SQL INSERT Command : Syntax

OR

SQL INSERT Command : Syntax

SQL INSERT Command : Example

• Inserting two values in each tables student_details and teacher_details of the

database db_school.

For teacher_details Table

Query-1 : INSERT Into teacher_details Values(101, ‘Anurag’, ‘TGT’);

SQL/ Sumati/BCA/Trident 10
Query-2 : INSERT Into teacher_details Values(102, ‘Anoop’, ‘PGT’);

For student_details Table

To display the data, execute the below command.

Query : SELECT * From student_details;

• In this way, one can insert as much data as possible in the database.

• The thing one must remember while inserting the data in the database is the order
of columns. If value of roll_no is inserted into name or vice-versa, the query will not
execute.

• Also, values should be inserted according to the data types mentioned while creating
the tables.

SQL UPDATE Command : Introduction

• SQL UPDATE command is a must have command while dealing with large data set.
If by chance, any values in the table are inserted incorrectly, UPDATE command is
there to help you out.

• The UPDATE command is used to “Update or Modify” the existing values/records

in the database tables. The records which needs to be modified can be a single one
or multiple values which can be manipulated based upon the query used.

• Also, with the help of SQL Clauses, modification on specific rows/values can also be
done.

SQL UPDATE Command : Syntax

• The syntax of UPDATE Command in SQL looks like :

SQL UPDATE Command : Syntax

SQL/ Sumati/BCA/Trident 11
SQL UPDATE Command : Example

• Consider the table of student_details and teacher_details.

Table : student_details

Table : teacher_details

Example-1 : Now if we wish to update marks of student “SAURAV”. Then, it can be done
by executing the below query.

Query : UPDATE student_details SET marks = ’70’ where roll_no = 1;

Output :

Example-2 : If we wish to update the name of the teacher. Then it can be done by executing
below query.

Query : UPDATE teacher_details SET name = ‘Ganesh’ where E.ID = 102;

SQL/ Sumati/BCA/Trident 12
Output :

SQL DELETE Command : Introduction

• The SQL DELETE Command is a type of SQL SDML Command and is used to
delete pre-existing records or values or rows of the tables present in the database.

• The advantage of using SQL DELETE Command is, only the values or records
from table gets deleted and the overall structure(containing attributes) remains
untouched. Let’s check the syntax first and then we will try to implement this
command with an example.

SQL DELETE Command : Syntax

• The syntax for SQL DELETE Command is pretty simple. There are two possible
basic syntax based on conditions. Below is their illustration.

SQL DELETE Command : Syntax

OR

SQL/ Sumati/BCA/Trident 13
 SQL DELETE Command : Syntax

SQL DELETE Command : Example

• Consider the table of student_details that was created earlier.

Table : student_details

Example-1 : If we wish to delete the whole student_details table, it can be done by

executing below query.

Query : DELETE FROM student_details;

Example-2 : If we wish to delete a particular row from the student_details, it can be done
by executing below query.

Query : DELETE FROM student_details WHERE Name = ‘Rakesh’;

NOTE : As the structure of table remains untouched even after deletion of table data. If
we need to insert values in the table again, it can be done without creating a new table and
by directly inserting values to the table through INSERT command.

SQL TRUNCATE Command : Introduction

• SQL TRUNCATE Command is a type of SQL DDL command and is used to delete
the pre-existing tables from the databases.

SQL/ Sumati/BCA/Trident 14
 • The difference between SQL DELETE and SQL TRUNCATE Command is, there is
no room for deleting specific values/rows from the table with the help of
TRUNCATE command. Only the whole table can be deleted and not the specific
rows/values.

SQL TRUNCATE Command : Syntax

• The syntax for SQL TRUNCATE command can be depicted as :

SQL TRUNCATE Command : Syntax

SQL TRUNCATE Command : Example

• Consider the student_details and teacher_details table over which we need to

perform TRUNCATE command.

Table : student_details

Table : teacher_details

Example-1 : We wish to delete the student_details table. It can be done by executing below
query.

SQL/ Sumati/BCA/Trident 15
Query : TRUNCATE Table student_details;

SQL DROP Command : Introduction

• SQL DROP Command is also a SQL DDL Command and is used to perform same
functionality as TRUNCATE command. i.e. to delete the existing table completely
from the database leaving nothing behind.

• It means, all the values, attributes, rows, column and overall structure of the table is
deleted completely.

• If we wish to insert values in the same table, it need to be recreated first and then
only the values can be inserted.

• DROP Command once executed cannot be rolled back. It means, once the table is
deleted, it cannot be retrieved again.

SQL DROP Command : Syntax

• The syntax for SQL DROP Command is quiet similar to that of TRUNCATE
command except for the DROP keyword that is used in DROP command.

SQL DROP Command : Syntax

SQL DROP Command : Example

• Consider the student_details and teacher_details table over which we need to

perform DROP command.

SQL/ Sumati/BCA/Trident 16
Table : student_details

Table : teacher_details

Example-1 : We wish to delete the student_details table. It can be done by executing below
query.

Query : DROP Table student_details;

NOTE : As the structure of table is deleted completely, If we need to insert values in the
table again, it cannot be done without creating a new table. Hence, new table needs to
created first and then values to the table can be inserted through INSERT command.

SQL ALTER Command : Introduction

• The SQL ALTER Command is an exciting command to use. It is used to ALTER or

modify the overall structure of tables inside the database rather than altering the
values of tables.

SQL ALTER Command : Syntax

• The syntax of ALTER Command varies depending upon the type of alteration the
user wants. The syntax for each is illustrated below.

SQL/ Sumati/BCA/Trident 17
 SQL ALTER Command : Types

SQL ALTER Command : Example

• Consider the table of student_details over which all the above commands will be
executed.

Table : student_details

Example-1 : Adding a new column of “Location” in student_details table.

Query : ALTER Table student_details ADD Location varchar(20);

Output :

SQL/ Sumati/BCA/Trident 18
Note : The values in column “Location” are left blank because had added the column to
the table and not inserted the values in it.

Example-2 : Modifying data type of column “Name” in the student_details table.

Query : ALTER Table student_details MODIFY COLUMN Name char(10);

Output : You won’t see any change in view of table. But, while inserting new values, only
character values will be inserted in the “Name” column.

Example-3 : Dropping a column “Marks” from student_details table.

Query : ALTER Table student_details DROP COLUMN Marks;

Output :

Example-4 : Adding Primary Key in student_details table.

Query : ALTER Table student_details ADD CONSTRAINT C1 PRIMARY KEY

(Roll_No);

Output :

SQL/ Sumati/BCA/Trident 19
Note : A Primary Key is an attribute of a table with the help of which each data record
present in the table can be uniquely identified. To know more about, please refer KEYS IN
DBMS.

SQL Constraints : Introduction

• SQL Constraints are the “Limits or Rules” that the users can specify while creating
the tables and inserting values in it. Constraints means “Restrictions” in data which
ensures better performance of the databases.

• Also, SQL Constraints restricts the type of data that is to be inserted in the table of
the database allowing better results while executing SQL queries.

• The SQL Constraints that can be used are listed and explained below.

SQL Constraints : Types

SQL/ Sumati/BCA/Trident 20
SQL Constraints
SQl Constraints are rules used to limit the type of data that can go into a table, to maintain the
accuracy and integrity of the data inside table.Constraints can be divided into following two types,

• Column level constraints : limits only column data

• Table level constraints : limits whole table data

With Table level Contraint,we can define constraint for multiple columns where as for
Column level (inline) we can define constraint only for specific column only.

Column-level constraints refer to a single column in the table and do not specify a
column name (except check constraints). ... Table-level constraints refer to one or
more columns in the table. Table-level constraints specify the names of the columns
to which they apply.

Constraints are used to make sure that the integrity of data is maintained in the database. Following
are the most used constraints that can be applied to a table.

• NOT NULL

• UNIQUE

• PRIMARY KEY

• FOREIGN KEY

• CHECK

• DEFAULT

NOT NULL Constraint

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is
applied to a column, you cannot pass a null value to that column. It enforces a column to contain a
proper value. One important point to note about NOT NULL constraint is that it cannot be defined at
table level.

SQL/ Sumati/BCA/Trident 21
Example using NOT NULL constraint

CREATE table Student(s_id int NOT NULL, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will not take NULL value.

ALTER TABLE table_name MODIFY ( column_name NOT NULL);

UNIQUE Constraint

UNIQUE constraint ensures that a field or column will only have unique values. A UNIQUE
constraint field will not have duplicate data. UNIQUE constraint can be applied at column level or
table level.

Example using UNIQUE constraint when creating a Table

CREATE table Student(s_id int NOT NULL UNIQUE, Name varchar(60), Age int);

The above query will declare that the s_id field of Student table will only have unique values and
wont take NULL value.

Example using UNIQUE constraint after Table is created

ALTER table Student add UNIQUE(s_id);

The above query specifies that s_id field of Student table will only have unique value.

Primary Key Constraint

SQL/ Sumati/BCA/Trident 22
Primary key constraint uniquely identifies each record in a database. A Primary Key must contain
unique value and it must not contain null value. Usually Primary Key is used to index the data inside
the table.

Example using PRIMARY KEY constraint

CREATE table Student (s_id int PRIMARY KEY, Name varchar(60) NOT NULL, Age int);

The above command will creates a PRIMARY KEY on the s_id.

Example using PRIMARY KEY constraint

ALTER table Student add PRIMARY KEY (s_id);

The above command will creates a PRIMARY KEY on the s_id.

ALTER TABLE table_name

ADD CONSTRAINT constraint_name constraint type (columnname);

Example
Let's look at an example of how to add a unique constraint to an existing table in Oracle using the
ALTER TABLE statement.

ALTER TABLE supplier

ADD CONSTRAINT supplier_unique UNIQUE (supplier_id);

Drop Unique Constraint

The syntax for dropping a unique constraint in Oracle is:

ALTER TABLE table_name

DROP CONSTRAINT constraint_name;

SQL/ Sumati/BCA/Trident 23
Example
Let's look at an example of how to remove a unique constraint from a table in Oracle.

ALTER TABLE supplier

DROP CONSTRAINT supplier_unique;

ALTER TABLE primarytest

DROP unique;

CHECK Constraint

CHECK constraint is used to restrict the value of a column between a range. It performs check on
the values, before storing them into the database. Its like condition checking before saving data into
a column.

Example using CHECK constraint at Table Level

create table Student(s_id int NOT NULL CHECK(s_id > 0),

Name varchar(60) NOT NULL,

Age int);

The above query will restrict the s_id value to be greater than zero.

Example using CHECK constraint

ALTER table Student add CHECK(s_id > 0);

Example using DEFAULT constraint

CREATE TABLE tablename (

SQL/ Sumati/BCA/Trident 24
Columnname datatype DEFAULT 'defaultvalue' );

ALTER TABLE Persons

MODIFY City DEFAULT 'Sandnes';

Foreign Key Constraint

FOREIGN KEY is used to relate two tables. FOREIGN KEY constraint is also used to restrict actions
that would destroy links between tables. To understand FOREIGN KEY, let's see it using two table.

Customer_Detail Table :

c_id Customer_Name address

101 Adam Noida

102 Alex Delhi

103 Stuart Rohtak

Order_Detail Table :

Order_id Order_Name c_id

SQL/ Sumati/BCA/Trident 25
 10 Order1 101

11 Order2 103

12 Order3 102

In Customer_Detail table, c_id is the primary key which is set as foreign key in Order_Detail table.
The value that is entered in c_id which is set as foreign key in Order_Detail table must be present
in Customer_Detail table where it is set as primary key. This prevents invalid data to be inserted
into c_id column of Order_Detail table.

Example using FOREIGN KEY constraint

CREATE table Order_Detail(order_id int PRIMARY KEY,

order_name varchar(60) NOT NULL,

c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id));

In this query, c_id in table Order_Detail is made as foriegn key, which is a reference of c_id column
of Customer_Detail.

Example using FOREIGN KEY constraint

ALTER table Order_Detail add FOREIGN KEY (c_id) REFERENCES Customer_Detail(c_id);

Behaviour of Foriegn Key Column on Delete

There are two ways to maintin the integrity of data in Child table, when a particular record is deleted
in main table. When two tables are connected with Foriegn key, and certain data in the main table is

SQL/ Sumati/BCA/Trident 26
deleted, for which record exit in child table too, then we must have some mechanism to save the
integrity of data in child table.

• On Delete Cascade : This will remove the record from child table, if that value of foriegn key is
deleted from the main table.

• On Delete Null : if a record in the parent table is deleted, then the corresponding records in the
child table will have the foreign key fields set to null..

• If we don't use any of the above, then we cannot delete data from the main table for which data
in child table exists. We will get an error if we try to do so.

ERROR : Record in child table exist

CREATE TABLE products

( product_id numeric(10) not null,
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE
);

SQL/ Sumati/BCA/Trident 27
ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;

Here is an example of column-level constraints:

CREATE TABLE mytable(name CHAR(10) NOT NULL,
id INTEGER REFERENCES idtable(id),
age INTEGER CHECK (age > 0));
Note: Multiple column constraints are separated by a space.
Here is an example of table-level constraints:
CREATE TABLE yourtable(firstname CHAR(20) NOT NULL,
lastname CHAR(20) NOT NULL,
UNIQUE(firstname, lastname));

Rename column in table

(NEW in Oracle 9i Release 2)

Syntax

Starting in Oracle 9i Release 2, you can now rename a column.

To RENAME A COLUMN in an existing table, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name

RENAME COLUMN old_name TO new_name;

Example

Let's look at an example that shows how to rename a column in an Oracle table using
the ALTER TABLE statement.

SQL/ Sumati/BCA/Trident 28
For example:

ALTER TABLE customers

RENAME COLUMN customer_name TO cname;

This Oracle ALTER TABLE example will rename the column

called customer_name to cname.

Rename table

Syntax

To RENAME A TABLE, the Oracle ALTER TABLE syntax is:

ALTER TABLE table_name

RENAME TO new_table_name;

Example

Let's look at an example that shows how to rename a table in Oracle using the ALTER
TABLE statement.

For example:

ALTER TABLE customers

RENAME TO contacts;

SQL Functions : Introduction

• SQL functions are special classical functions that are used to perform specific tasks
when combined in with SQL queries. They are basically arithmetic functions.

SQL/ Sumati/BCA/Trident 29
 • They are used in tables with columns having numerical values. There exists five
SQL functions which are majorly used and all of them are listed and explained
below.

SQL Functions : Types

SQL Functions : SUM

• The “SUM” function as the name suggests, is used to calculate sum for a particular
column. Below is the syntax and example.

Syntax : SELECT SUM(Column_Name) From Table_Name Where “Condition”;

Example : Consider the student_details table. We can find the total sum of marks of
students by using below query.

Table : student_details

Query : SELECT SUM(Marks) From student_details;

Output :

SQL/ Sumati/BCA/Trident 30
SQL Functions : AVG

• The AVG (Average) function as the name suggests is used to calculate average for a
particular column containing numeric values.

Syntax : SELECT AVG(Column_Name) From Table_Name Where “Condition”;

Example : Consider the student_details table. We can calculate the average of marks for
the students using “AVG” function using below query.

Table : student_details

Query : SELECT AVG(Marks) From student_details table;

Output :

SQL Functions : COUNT

• The "COUNT" function calculates the number of rows that exists in a column
which follows a particular condition.

Syntax : SELECT COUNT(Column_Name) From Table_Name Where “Condition”;

Example : Consider the student_details table. If we want to “COUNT” the total number of
students present in the table, it can be done using below query.

Table : student_details

SQL/ Sumati/BCA/Trident 31
Query : SELECT COUNT(Roll_No) From student_details;

Output :
SQL Functions : MIN

• The SQL “MIN” function is used to return smallest possible value from a particular
column containing numeric values.

Syntax : SELECT MIN(Column_Name) From Table_Name Where “Condition”;

Example : Consider the student_details table. If we want to select the minimum marks of a
student, it can be done by executing below query.

Table : student_details

Query : SELECT MIN(Marks) From student_details;

Output :

SQL Functions : MAX

• The SQL “MAX” function is used to return largest possible value from a particular
column containing numeric values.

Syntax : SELECT MAX(Column_Name) From Table_Name Where “Condition”;

SQL/ Sumati/BCA/Trident 32
Example : Consider the student_details table. If we want to select the maximum marks of
a student, it can be done by executing below query.

Table : student_details

Query : SELECT MAX(Marks) From student_details;

Output :

The SQL GROUP BY Statement

The GROUP BY statement groups rows that have the same values into summary rows, like "find
the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM,
AVG) to group the result-set by one or more columns.

GROUP BY Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

SQL/ Sumati/BCA/Trident 33
Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

CustomerI CustomerNa ContactNa Address City PostalCo Countr

D me me de y

1 Alfreds Maria Obere Str. Berlin 12209 German

Futterkiste Anders 57 y

2 Ana Trujillo Ana Trujillo Avda. de la Méxic 05021 Mexico

Emparedados Constitució o D.F.
y helados n 2222

3 Antonio Antonio Mataderos Méxic 05023 Mexico

Moreno Moreno 2312 o D.F.
Taquería

4 Around the Thomas 120 Londo WA1 1DP UK

Horn Hardy Hanover Sq. n

5 Berglunds Christina Berguvsväg Luleå S-958 22 Sweden

snabbköp Berglund en 8

SQL GROUP BY Examples

The following SQL statement lists the number of customers in each country:

Example

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country;

SQL/ Sumati/BCA/Trident 34
 Oracle HAVING Example: (with GROUP BY MIN function)

Let's take a table "employees"

Employees table:

1. CREATE TABLE "EMPLOYEES"

2. ( "EMP_ID" NUMBER,

3. "NAME" VARCHAR2(4000),

4. "AGE" NUMBER,

5. "DEPARTMENT" VARCHAR2(4000),

6. "SALARY" NUMBER

7. )

8. /

Execute this query:

1. SELECT department,

2. MIN(salary) AS "Lowest salary"

3. FROM employees

4. GROUP BY department

SQL/ Sumati/BCA/Trident 35
5. HAVING MIN(salary) < 15000;

Output

Oracle HAVING Example: (with GROUP BY MAX function)

Execute this query:

1. SELECT department,

2. MAX(salary) AS "Highest salary"

3. FROM employees

4. GROUP BY department

5. HAVING MAX(salary) > 30000;

Output:

SQL JOINS : Introduction

• SQL JOINS are the special clauses that are used to combine multiple tables present
in a database on the basis of some common attributes present in those tables.

• The SQL JOINS has the ability of combining two or more data tables/tuples into a
single table/table only if the following conditions are satisfied.

o There must be a common attribute in both(tables which are participating)

tables.

o Join condition must be satisfied.

• The common attributes are compared using SQL Operators based upon the
required conditions. The most oftenly used operator is the “=” (Equal To) symbol.

SQL/ Sumati/BCA/Trident 36
 • Also, SQL Joins uses “ON” clause. There are Five basic SQL Joins each of whose
syntax and examples are explained in this chapter.

SQL Joins : Types

SQL JOINS : The INNER Join

• The INNER JOIN which is also known as “SIMPLE JOIN or EQUIJOIN” is the
most commonly used join in SQL. Queries equipped with INNER JOIN when
executed returns all the rows which are present in the tables corresponding to the
common attribute.

Syntax : SELECT Column_Name From Table_Name1 INNER JOIN Table_Name2 ON

Table_Name1.Column_Name = Table_Name2.Column_Name;

For Example : Consider two tables student_details and student_result.

SQL/ Sumati/BCA/Trident 37
Table : student_details

Table : student_result

Query : SELECT * From student_details INNER JOIN student_result Where

student_details.Roll_No = student_result.Roll_No;

Output :

SQL JOINS : The NATURAL Join

• The NATURAL JOIN is similar to INNER JOIN. The only difference is, redundant
columns present in INNER JOINS are removed when NATURAL JOIN is used.

• It works only if, one attribute or more than one attributes are common between the
joining/participating relations.

Syntax : SELECT Column_Name From Table_Name1 NATURAL JOIN Table_Name2;

For Example : Consider two tables student_details and student_result.

SQL/ Sumati/BCA/Trident 38
 Query : SELECT * From student_details NATURAL JOIN student_result;

Output

Difference between Natural JOIN and INNER JOIN in SQL :

SR.NO. NATURAL JOIN INNER JOIN

Inner Join joins two table on the basis of the

Natural Join joins two tables based on column which is explicitly specified in the ON
1. same attribute name and datatypes. clause.

In Natural Join, The resulting table

will contain all the attributes of both In Inner Join, The resulting table will contain
the tables but keep only one copy of all the attribute of both the tables including
2. each common column duplicate columns also

SYNTAX: SYNTAX:
SELECT * SELECT *
FROM table1 NATURAL JOIN FROM table1 INNER JOIN table2 ON
3. table2; table1.Column_Name = table2.Column_Name;

SQL JOINS : LEFT OUTER JOIN

• There exists a concept of position(left or right) of relations in case of

both LEFT and RIGHT OUTER JOIN.

SQL/ Sumati/BCA/Trident 39
 • To implement LEFT OUTER JOIN, at least one entity needs to be common in the
relations. All the attributes/tuples present in the left relation are recorded in the
resulting relation along with those which are commonly present in the right relation.

• If in case any tuple in left relation does not matches with the tuple in right
relation, NULL value will be displayed against that tuple in the resulting relation.

Syntax : SELECT Column_Name From Table_Name1 LEFT JOIN Table_Name2 ON

Table_Name1.Column_Name = Table_Name2.Column_Name;

For Example : Consider the tables student_details and student_result. Now, if we want to
implement left outer join on these relations, the result will look like:

Query : SELECT Roll_No, Name, Address, Subject, Marks From Student_Details LEFT
OUTER JOIN Student_Result ON Student_Details.Roll_No = Student_Result.Roll_No;

SQL JOINS : The RIGHT OUTER Join

• There exists a concept of position(left or right) of relations in case of

both LEFT and RIGHT OUTER JOIN.

• The RIGHT OUTER JOIN is completely similar to left outer join except the
resulting relation will include all the tuples from relation present on right hand
relation.

• Also, NULL value will be displayed against the tuple which doesn’t matches up with
the left side relation.

Syntax : SELECT Column_Name From Table_Name1 RIGHT JOIN Table_Name2 ON

Table_Name1.Column_Name = Table_Name2.Column_Name;

SQL/ Sumati/BCA/Trident 40
For Example : Consider the tables Student_Details and Student_Result. Now, if we want to
implement right outer join on these relations, the result will look like:

Query : SELECT Roll_No, Name, Address, Subject, Marks From Student_Details RIGHT
OUTER JOIN Student_Result ON Student_Details.Roll_No = Student_Result.Roll_No;

SQL JOINS : The FULL OUTER Join

• In FULL OUTER JOIN, both the relations are merged together which results in a
relation consisting of all the tuples.

• If in case, tuples doesn’t matches, NULL value is passes against that.

Syntax : SELECT Column_Name From Table_Name1 FULL OUTER JOIN Table_Name2

ON Table_Name1.Column_Name = Table_Name2.Column_Name;

For example : Consider the tables Student_Details and Student_Result. Now, if we want to
implement full outer join on these relations, the result will look like:

Query : SELECT Roll_No, Name, Address, Subject, Marks From Student_Details FULL
OUTER JOIN Student_Result ON Student_Details.Roll_No = Student_Result.Roll_No;

SQL/ Sumati/BCA/Trident 41
NOTE : In the above description of various joins, some symbols are used in the screenshots
of examples. They all have some certain meaning.

1. ⋈: Natural Join

2. : Left Outer Join

3. : Right Outer Join

4. : Full Outer Join

A self join is a regular join but the table is joined with itself. This is useful when we want to
compare rows within the same table.

Syntax of Self Join:

SELECT A.column_name, B.column_name

FROM table_name A, table_name B

WHERE condition;

• Here, A and B are aliases for the same table.

1. Employees and Managers

o A table Employees(emp_id, emp_name, manager_id)

o You can use self join to find employee names along with their manager names.

SQL/ Sumati/BCA/Trident 42
SELECT E1.emp_name AS Employee, E2.emp_name AS Manager

FROM Employees E1, Employees E2 where E1.manager_id = E2.emp_id;

emp_id emp_name manager_id

1 Alice NULL

2 Bob 1

3 Carol 1

Using self join:

Employee Manager

Bob Alice

Carol Alice

SQL/ Sumati/BCA/Trident 43