SQL - Joins
Dr. Louay (Louie) Chebib
06-Feb-2018
Abstract the complexity!
The role of good technology is to abstract the complexity and to blend into the background so that the user can accomplish their task rather than be distracted by the tool.
Review
What is a database?
Abstract the complexity!
What is Table?
What is SQL?
Review
What is a database?
A database is a set of data that has a regular structure and that is organized in such a way that a computer can easily find the desired information.
Abstract the complexity!
What is Table?
A table is a collection of related data held in a structured format within a database. It consists of columns, and rows.
What is SQL?
SQL (pronounced "ess-que-el") stands for Structured Query Language. SQL is used to communicate with a database.
Sources: linfo.org, sqlcourse.com, wikipedia.org
SQL
select statement:
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
Abstract the complexity!
Sources: w3schools.com
Syntax:
SELECT column1, column2, ...
FROM table_name;
WHERE condition;
e.g. SELECT * FROM table_name;
Try it:
SQL
SQL Join Clause
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Abstract the complexity!
Sources: w3schools.com
Types of the Joins in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
Abstract the complexity!
Sources: wikimedia.org
SQL
The INNER JOIN keyword selects records that have matching values in both tables.
Abstract the complexity!
Sources: w3schools.com
Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Try it:
SQL
The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2).
The result is NULL from the right side, if there is no match.
Abstract the complexity!
Sources: w3schools.com
Syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Try it:
SQL
The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1).
The result is NULL from the left side, when there is no match.
Abstract the complexity!
Sources: w3schools.com
Syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Try it:
May not be supported on all RDBMS engines.
SQL
The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Abstract the complexity!
Sources: w3schools.com
Syntax:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
May not be supported on all RDBMS engines.
SQL - Joins
Workshop - Exercise
Using SQLiteStudio: (https://sqlitestudio.pl/index.rvt)
Try it:
Look over the sample tables.
Try selecting using joins.
Save your results to new tables.
Did all the join types work?
Abstract the complexity!
Sources: w3schools.com
SQL Joins
By Dr. L. Chebib
