Inner join and outer join

What is SQL ?
SQL stands for Structured Query
Language.
SQL is a special-purpose programming
language designed for managing data held
in a relational database management
system (RDBMS).
SQL is used to communicate with a
database and lets you access and
manipulate databases.

SQL statements are
used to perform tasks
such as update data
on a database, or
retrieve data from a
database.
Some common
relational database
management systems
that use SQL are:
Oracle, Sybase,
Microsoft SQL Server,
and Access.

SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
What can SQL do ?

What is a Relational Database
System ?
A relational database system contains one
or more objects called tables.
The data or information for the database
are stored in these tables.
Tables are uniquely identified by their
names and are comprised of columns and
rows.

Columns contain the column name, data
type, and any other attributes for the
column.
Rows contain the records or data for the
columns.
City High Low
Manila 101 92
Makati 88 69
Quezon City 77 60
Sample Table “Weather Temperature”
Columns
Rows

One of the most beneficial features of SQL
& relational database systems
Makes relational database systems
"relational".
Used to combine rows from two or more
tables, based on a common field between
them.
Allows you to link data from two or more
tables together into a single query result--
from one single SELECT statement.

What would happen if you worked with
one table only, and didn't have the
ability to use “JOIN". Let's say you have
a one-table database that is used to
keep track of all of your customers and
what they purchase from your store:
CID_No Surname First Address Date Item Price

Every time a new row is inserted into the
table, all columns will be updated, thus
resulting in unnecessary redundant data. For
example, every time Juan Tamad purchases
something, the following rows will be inserted
into the table:
CID_No. Surname First Address Date Item Price
10908 Tamad Juan 14 Sipag St., Manila 01/07/14 1K Rice 40.00
10908 Tamad Juan 14 Sipag St., Manila 01/08/14
Purefoods
Corned Beef
85.00
10908 Tamad Juan 14 Sipag St., Manila 01/09/14 555 Sardines 25.00

To eliminate unnecessary redundant data, our
database should have two tables :
1. One for keeping track of your customer
2. And the other to keep track of what they
purchase :
CID_No Surname First Address
CID_No Date Item Price
TABLE 1
Cust_Info Table
TABLE 2
Purchases Table
Now, whenever a purchase is made from a repeating
customer, the 2nd table, "Purchases" only needs to
be updated , eliminating useless redundant data.

Notice that both tables have a common “CID_No"
column.
CID _No Date Item Price
TABLE 1
Cust_Info Table
TABLE 2
Purchases Table
Table 1
Cust_Info
Table 2
Purchases
CID_No
This column, which contains the unique customer
id number, will be used to JOIN the two tables.

The INNER JOIN keyword selects all rows from
both tables as long as there is a match between the
columns in both tables.
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
Notice that each of the columns are always
preceded with the table name and a period.
This is a good practice so that you wont
confuse which columns go with what tables.
It is also required if the name column names
are the same between the two tables.

SELECT cust_info.first, cust_info.surname,
purchases.item
FROM cust_info
INNER JOIN purchases
ON cust_info.cid_no = purchases.cid_no;
To JOIN the Cust_Info Table and the
Purchases Table using the CID_No
column that is found in both tables, we
have :
column_name(s)
table1
table 2
table1.column_name table2.column_name

If we execute these statements, using the data in
the tables below :
10907 Clavio Arnold 7 Tahimik St, Quezon City
10908 Tamad Juan 14 Sipag St. Malate, Manila
10909 Santos Charo Laya Ave., Muntinlupa City
TABLE 1
Cust_Info Table
CID_No Date Item Price
10908 01/07/14 1K rice 40.00
10909 01/07/14 Papaya Soap 140.00
10907 01/07/14 1K Ampalaya 60.00
10908 01/08/14 Purefoods Corned Beef 85.00
10907 01/08/14 5K rice 350.00
10908 01/09/14 555 Sardines 25.00
TABLE 2
Purchases Table

We will have something like this :
First Surname Item
Arnold Clavio 1K Ampalaya
Arnold Clavio 5K rice
Juan Tamad 1K rice
Juan Tamad Purefoods Corned Beef
Juan Tamad 555 Sardines
Charo Santos Papaya Soap

TABLE ORDER :
STATEMENT 1 STATEMENT 2
SELECT * SELECT*
FROM Table1 FROM Table2
INNER JOIN Table2 INNER JOIN Table1
ON Table1.column_name ON Table2.column_name
*Table2.column_name *Table1.column_name

SPECIFIED COLUMNS :
SELECT specified_column/s
FROM Table1
INNER JOIN Table2
ON Table1.column_name = Table2.column_name
SELECT * FROM Table1,Table2
WHERE Table1.column_name = Table2.column_name
ALTERNATIVE :

INNER JOIN requires the existence of a match
between the tables based on specified column/s
and only matching records are selected.
The OUTER JOIN keyword does not require each
record in the two joined tables to have a matching
record. The joined table retains each record—
even if no other matching record exists.

LEFT JOIN - Returns all rows from the left
table for the first tables. Even if there are no
matches in the right.
Depending on which table’s rows are retained,
OUTER JOIN is subdivided into :

RIGHT JOIN - Return all the rows from the
right table ( or the second table) even if there
are no matches in the left table

FULL JOIN - Both tables are secondary (or
optional) such that if rows are being matched
in tabled A and table B then all rows from table
A are displayed even if there is no matching
row in table B and vice versa

Look at the tables below and note the
common column names between them :
EmployeeID EmpName
01-054 Jerry Mendoza
01-055 Dane Sevilla
01-056 Mylene Antonio
LogID EmployeeID LogDate
1 01-054 11-09-11
2 01-056 11-10-11
3 01-054 11-11-11
TimeRecID LogID TIn TOut
1 1 09:00 18:03
2 2 07:30 16:55
3 3 09:02 18:34
TABLE 1
Employee Table
TABLE 2
LogRecordsTable
TABLE 3
TimeRecordsTable

SELECT * FROM Employee AS e
LEFTJOIN LogRecords AS L
ON e.EmployeeID = L.EmployeeID
LEFT JOIN TimeRecords AS t
ON t.LogID = L.LogID
Employee
Table 1
e
LogRecords
Table 2
L
TimeRecords
Table 3
t

Employee
ID
EmpName LogID EmployeeID LogDate TimeRecID LogID TIn TOut
01-054
Jerry
Mendoza
1 01-054 11-09-11 1 1 09:00 18:03
01-054
Jerry
Mendoza
3 01-054 11-11-11 3 3 9:02 18:34
01-055
Dane
Sevilla
Null Null Null Null Null Null Null
01-056
Mylene
Antonio
2 01-056 11-10-11 2 2 7:30 16:55
NULL is returned where
no there are no matches
in the RIGHT table

SELECT e.EmpName, L.LogDate, t.Tin, t.Tout
FROM Employee AS e
EmpName LogDate TIn TOut
Jerry Mendoza 11-09-11 09:00 18:03
Jerry Mendoza 11-11-11 9:02 18:34
Dane Sevilla Null Null Null
Mylene Antonio 11-10-11 7:30 16:55

FROM Employee AS e
WHERE L.LogDate IS NOT NULL
Jerry Mendoza 11-09-11 09:00 18:03
Jerry Mendoza 11-11-11 9:02 18:34
Mylene Antonio 11-10-11 7:30 16:55

FROM TimeRecords AS t
RIGHT JOIN LogRecords AS L
RIGHT JOIN Employee AS e
Jerry Mendoza 11-09-11 09:00 18:03
Jerry Mendoza 11-11-11 9:02 18:34
Dane Sevilla Null Null Null
Mylene Antonio 11-10-11 7:30 16:55

In FULL JOIN, both tables are secondary (or
optional). In this case, if we are matching
rows in table A and B, then we display :
all rows from
table A even
if there is no
matching row
in table B
all rows from table B even if there is no
matching row in table A
TABLE A TABLE B

Look at the tables below and note the
common column names between them :
GenreCode GenreDesc
HR Horror
RC Romance
HS History
CY Comedy
SF Sci-Fi
MovieID MovieTitle GenreCode
1 Saw 4 HR
2 Bad Teacher CY
3 The Notebook RC
4 Shrek No code available
5 Schindler’s List HS
6 The Exorcist HR
TABLE 1
MovieList
TABLE 2
GenresTable

SELECT MovieTitle, GenreDesc
FROM MovieList AS m
FULLJOIN Genres AS g
ON m.GenreCode = g.GenreCode
MovieTitle GenreDesc
Saw 4 Horror
Bad Teacher Comedy
The Notebook Romance
Shrek NULL
Schindler’s List History
The Exorcist Horror
NULL Sci-Fi

JOIN’s are used to combine & get the data from
different tables.
INNER JOIN returns rows when there is a match
in both tables.
LEFT JOIN returns all rows from the left table,
even if there are no matches in the right table.
RIGHT JOIN returns all rows from the right
table, even if there are no matches in the left
table.
FULL JOIN returns rows when there is a match
in one of the tables.

NULL values are returned in the output
when no records are returned by an OUTER
JOIN statement
JOIN queries can be used with the
conjunction of SELECT
We can also use WHERE clauses along with
JOIN’s

Identify the type of JOIN shown in the
following diagrams :

Identify the following :
1. This is used to communicate with a
database and lets you access and
manipulate databases.
2. This is used to combine and get data from
different tables.
3. This returns rows when there is a match in
one of the tables.

4. This returns rows when there is a match in
both tables.
5. This returns all rows from the left table,
even if there are no matches in the right
table.
6. This is where one or more objects called
tables are contained.
7. This allows you to link data from two or
more tables together into a single query
result from one single SELECT statement

8. This returns all rows from the right table,
even if there are no matches in the left
table.
9. This is value returned in the output when
no records are returned by an OUTER
JOIN statement.
10. This conjunction can be used with JOIN
queries.

Inner join and outer join

More Related Content

What's hot

Similar to Inner join and outer join

More from Nargis Ehsan

Recently uploaded

In this document

Inner join and outer join