Page 106 - PowerPoint Presentation

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

3 Daisy Female Indang
OrderID CustomerID OrderDate
101 1 2019-12-01

102 3 2019-12-04
103 2 2019-11-12

SELECT Customer_ID, CustomerName, Gender, City, OrderID, OrderDate FROM
Customers, Orders;
or
SELECT Customer_ID, CustomerName, Gender, City, OrderID, OrderDate FROM
Customers CROSS JOIN Orders;

The above query would produce the following result:
Customer_ID Customer_Name Gender City OrderID OrderDate
1 Christian Male Trece 101 2019-12-01
2 Nigel Male Indang 101 2019-12-01
3 Daisy Female Indang 101 2019-12-01
1 Christian Male Trece 102 2019-12-04
2 Nigel Male Indang 102 2019-12-04
3 Daisy Female Indang 102 2019-12-04
1 Christian Male Trece 103 2019-11-12
2 Nigel Male Indang 103 2019-11-12
3 Daisy Female Indang 103 2019-11-12

SQL INNER JOIN
The INNER JOIN selects records that have matching values in both tables.
The INNER JOIN creates a result by combining column values of two tables (table1 and
table2) based upon the join-condition. The query compares each record of table1 with every
record of table2 to find all pairs of records which satisfy the join-condition. When the join-
condition is satisfied, column values for each matched pair of records of table1 and able2 are
combined into a result record.
INNER JOIN is the same as JOIN, the keyword INNER is optional.
To perform join between two tables, there must be one column common between two tables.
Syntax:
SELECT table1.column1, table1.column2, table2.column1, table2.column2 FROM
table1_name INNER JOIN table2_name ON table1.column_name = table2.column_name;

Example: Consider the ‘Customers’ and ‘Orders’ tables having the following records.
CustomerID CustomerName Gender City
1 Christian Male Trece
2 Nigel Male Indang
3 Daisy Female Indang
4 Caiezl Female Alfonso
OrderID CustomerID OrderDate
101 1 2019-12-01

102 3 2019-12-04
103 2 2019-11-12

SELECT Customers.CustomerID, Customers.CustomerName, Customers.Gender,
Customers.City, Orders.OrderID, Orders.OrderDate FROM Customers INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

Page | 22