Scribd
Upload
4 views2 pages

SQL Database Join Examples and Queries

The document outlines the creation of a database named 'joinDB' with two tables: 'customer' and 'customerorder', including their respective fields and relationships. It provides sample data insertion for both tables and demonstrates SQL queries for inner join, left join, and right join operations to retrieve customer and order information. The document emphasizes the importance of using a different name than 'order' for the 'customerorder' table to avoid conflicts.

Uploaded by

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

SQL Database Join Examples and Queries

The document outlines the creation of a database named 'joinDB' with two tables: 'customer' and 'customerorder', including their respective fields and relationships. It provides sample data insertion for both tables and demonstrates SQL queries for inner join, left join, and right join operations to retrieve customer and order information. The document emphasizes the importance of using a different name than 'order' for the 'customerorder' table to avoid conflicts.

Uploaded by

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

create database joinDB;

Use joinDB;

CREATE TABLE customer(


customer_id INT PRIMARY KEY,
firstname VARCHAR(80),
lastname VARCHAR(80),
email VARCHAR(30),
address VARCHAR(100),
city VARCHAR(80)
);

CREATE TABLE customerorder( //note--do not use the name 'order'


order_id INT PRIMARY KEY,
orderdate DATE,
amount FLOAT,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customer(customer_id)
);

insert into customer values(1, 'George','Washington','gwashington@[Link]', '3200


Mt Vernon Hwy','Mount Vernon'),
(2, 'John','Adams','jadams@[Link]','1250 Hancock
St','Quincy'),
(3,'Thomas','Jefferson','tjefferson@[Link]','931 Thomas
Jefferson','Charlottesville'),
(4,'James','Madison','jmadison@[Link]','11350 Constitution
Hwy','Orange'),
(5,'James','Monroe','jmonroe@[Link]','2050 James
Monroe','Charlottesville');
insert into customerorder values
(1,'2023-07-04',234.56,1),
(2,'2023-03-14',78.50,3),
(3,'2023-05-23',124.00,2),
(4,'2023-03-14',65.50,3);

Inner join
---------
SELECT c.customer_id,[Link], [Link], [Link], [Link]
FROM customer c
INNER JOIN customerorder o
ON c.customer_id = o.customer_id;

Left join
--------
SELECT [Link], [Link], [Link], [Link]
FROM customer c
LEFT JOIN customerorder o
ON c.customer_id = o.customer_id;

SELECT [Link], [Link], [Link], [Link]


FROM customer c
LEFT JOIN customerorder o
ON c.customer_id = o.customer_id
WHERE [Link] IS NULL;
right join
----------
SELECT [Link], [Link], [Link], [Link]
FROM customer c
RIGHT JOIN customerorder o
ON c.customer_id = o.customer_id;

You might also like