Scribd
Upload
41 views19 pages

Praktikum Basis Data: Data Manipulation Language (DML)

SQL joins are used to query data from two or more related tables. Different types of joins include inner joins, left outer joins, right outer joins, and full outer joins. Inner joins return rows when there is a match between both tables. Left and right outer joins return all rows from the left or right table respectively and matched rows from the other table. Full outer joins return all rows when there is a match in either table.

Uploaded by

Angga Astawa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
41 views19 pages

Praktikum Basis Data: Data Manipulation Language (DML)

SQL joins are used to query data from two or more related tables. Different types of joins include inner joins, left outer joins, right outer joins, and full outer joins. Inner joins return rows when there is a match between both tables. Left and right outer joins return all rows from the left or right table respectively and matched rows from the other table. Full outer joins return all rows when there is a match in either table.

Uploaded by

Angga Astawa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 19

PRAKTIKUM BASIS DATA

DATA MANIPULATION LANGUAGE (DML)


SQL Joins
SQL joins are used to query data from two or more tables,
based on a relationship between certain columns in these
tables.

Tables in a database are often related to each other with


keys.

A primary key is a column (or a combination of columns)


with a unique value for each row. Each primary key value
must be unique within the table. The purpose is to bind
data together, across tables, without repeating all of the
data in every table.
Notice that the "CustomerID" column in the "Orders" table refers to
the "CustomerID" in the "Customers" table. The relationship between
the two tables above is the "CustomerID" column.
SELECT Orders.OrderID,
Customers.CompanyName,
Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
Different Types of SQL JOINs
• (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
SQL INNER JOIN Keyword
The INNER JOIN keyword return rows when there is at least one match in
both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
SELECT Orders.OrderID, Customers.CompanyName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;
JOIN Three Tables
The following SQL statement selects all orders with
customer and shipper information:

SELECT Orders.OrderID,
Customers.CompanyName,
Shippers.CompanyName
FROM Orders INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers
ON Orders.ShipVia = Shippers.ShipperID;
SELECT Orders.OrderID, Orders.OrderDate,
[Order Details].ProductID, Products.ProductName, [Order
Details].Quantity
FROM Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all rows from the left table (table_name1),
even if there are no matches in the right table (table_name2).

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
SELECT Customers.CompanyName, Orders.OrderID
FROM Customers LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CompanyName;
SELECT A.ProductID, A.ProductName, A.UnitPrice,
B.CompanyName, B.ContactName
FROM Products AS A LEFT JOIN Suppliers AS B
ON A.SupplierID = B.SupplierID

With a left join, SQL


Server includes in
the output all rows
in the first table
specified in the
SELECT statement
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all the rows from the right table
(table_name2), even if there are no matches in the left table
(table_name1).

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
SELECT Orders.OrderID, Employees.LastName,
Employees.FirstName
FROM Orders RIGHT JOIN Employees
ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
SELECT A.ProductID, A.ProductName, A.UnitPrice,
B.CompanyName, B.ContactName
FROM Products AS A RIGHT JOIN Suppliers AS B
ON A.SupplierID = B.SupplierID

Suppliers are
included whether
or not they have
associated
Products
SQL FULL JOIN Keyword
The FULL OUTER JOIN keyword return all records when there is a match in
either left (table1) or right (table2) table records.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
SELECT Customers.CompanyName, Orders.OrderID
FROM Customers FULL JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CompanyName;
SELECT A.ProductID, A.ProductName, A.UnitPrice,
B.CategoryID, B.CategoryName
FROM Products AS A FULL JOIN
Categories AS B ON A.CategoryID = B.CategoryID

A full join
combines the
behavior of the
left and right
outer joins.

All products appear in the output


regardless of whether they have
categories, and all categories appear in
the output whether or not they are
associated with products
THE END

You might also like