Page 105 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

The above query would produce the following result:
CustomerName Address City
Christian Langit Punta Tanza
Nigel Andam Kayquit Indang

SQL NOT EXISTS Operator
The following SQL statement selects all the customers from the ‘Customers’ table who has
not placed any order.
SELECT CustomerName, Address, City FROM Customers WHERE NOT EXISTS
(SELECT*FROM Orders WHERE Customers.CustomerID = Orders.CustomerID);

The above query would produce the following result:
CustomerName Address City
John Mark Aguado Trece

SQL JOINS
SQL JOINS are used to combine records from multiple tables, based on a common field
between them. A SQL JOIN is performed whenever two or more tables are listed in a SQL
statement.
Different Types of SQL JOINs:
- CARTESIAN JOIN (sometimes called CROSS JOIN) – returns the cartesian product of
the sets of records from the two or more joined tables.
- INNER JOIN (sometimes called simple JOIN) – returns records that have matching values
in both tables.
- LEFT OUTER JOIN (sometimes called simple LEFT JOIN) – returns all records from the
left table, and the matched records from the right table.
- RIGHT OUTER JOIN (sometimes called simple RIGHT JOIN) – returns all records from
the right table, and the matched records from the left table.
- FULL OUTER JOIN (sometimes called simple FULL JOIN) – returns all records when
there is a match in either left or right table. (not supported in MySQL)
- SELF JOIN – a SELF JOIN is a regular join, but the table is joined with itself.(not supported
in MySQL)

SQL CARTESIAN JOIN
The CARTESIAN JOIN or CROSS JOIN returns the cartesian product of the sets of records
from two or more joined tables. It means CARTESIAN JOIN will join each record of one table
to every records of another table.
This usually happens when the matching column or WHERE condition is not specified.
The output of a cartesian join is the number of records in the first table multiplied by the number
of records in the second table.
If WHERE clause is used with CARTESIAN JOIN, it functions like an INNER JOIN.
Syntax:
SELECT table1.column1, table1.column2, table2.column1, table2.column2 FROM
table1_name, table2_name;
or
SELECT table1.column1, table2.column2, table2.column1, table2.column2 FROM
table1_name CROSS JOIN table2_name;

Example: Consider the ‘Customers’ and ‘Orders’ tables having the following records.
Customer_ID CustomerName Gender City
1 Christian Male Trece
2 Nigel Male Indang

Page | 21