[go: up one dir, main page]
Scribd
Upload
21 views6 pages

ASSIGNMENT Database

Uploaded by

Ayesha Kashif
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
21 views6 pages

ASSIGNMENT Database

Uploaded by

Ayesha Kashif
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/ 6

ASSIGNMENT # 2

Q1. What are joins in SQL?


Joins in SQL are used to combine rows from two or more tables
based on a related column between them. Joins allow you to
retrieve data that is spread across multiple tables in a database.
Example:
Let's look at a selection from the "Orders" table:

Order ID Customer ID Order Date


10308 2 1996-09-18
10309 37 1996-09-19
10310 77 1996-09-20
Then, look at a selection from the "Customers" table:

Customer ID Customer Name Contact Name Country


1 Ali Khan Ali Lahore
2 Hamza Malik Hamza Multan
3 Faizan Mughal Faizan Lahore
Then, we can create the following SQL statement (that contains
an INNER JOIN), that selects records that have matching values
in both tables:
SELECT Orders.OrderID, Customers.CustomerName,
Orders.OrderDate
FROM Orders
INNER JOIN Customers ON
Orders.CustomerID=Customers.CustomerID;
Output:
Order ID Customer Name Order Date
10308 Ali Khan 9/18/1996
10365 Hamza Khan 11/27/1996
10383 Faizan Mughal 12/16/1996

Differences between Left Outer Join, Right


Outer Join, and Full Outer Join:
Left Outer Join:
 Returns all rows from the left table, and the matched rows
from the right table.
 If there are any rows in the left table that do not have
matches in the right table, those rows are also included in
the result set.
 The unmatched rows from the right table are filled with
NULL values.
 The syntax for a left outer join is:
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;

Here's an example to illustrate the differences:


Let's say we have two tables: customers and orders.
customers table:
customer_id name

1 John

2 Jane

3 Bob

4 Alice
orders table:
order_id customer_id order_amount

1 1 100

2 1 200

3 2 50

4 4 75

Example of Left Outer Join:


SELECT customers.name, orders.order_amount
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;
This query will return:

name order_amount

John 100

John 200

Jane 50

Bob NULL

Alice 75

Right Outer Join:


 Returns all rows from the right table, and the matched
rows from the left table.
 If there are any rows in the right table that do not have
matches in the left table, those rows are also included in
the result set.
 The unmatched rows from the left table are filled with
NULL values.
 The syntax for a right outer join is:
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example of Right Outer Join:


SELECT customers.name, orders.order_amount
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;
This query will return:

name order_amount

John 100

John 200

Jane 50

Alice 75

Full Outer Join:


 Returns all rows from both the left and right tables.
 If there are any rows in either table that do not have
matches in the other table, those rows are also included in
the result set.
 The unmatched rows from both tables are filled with NULL
values.
 The syntax for a full outer join is:
SELECT column1, column2, ...
FROM table1
FULL JOIN table2
ON table1.column = table2.column;

Example of Full Outer Join:


SELECT customers.name, orders.order_amount
FROM customers
FULL JOIN orders
ON customers.customer_id = orders.customer_id;
This query will return:
name order_amount

John 100

John 200

Jane 50

Bob NULL

Alice 75

You might also like