Scribd
Upload
34 views2 pages

SQL Joins for Beginners

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
34 views2 pages

SQL Joins for Beginners

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','[email protected]', '3200


Mt Vernon Hwy','Mount Vernon'),
(2, 'John','Adams','[email protected]','1250 Hancock
St','Quincy'),
(3,'Thomas','Jefferson','[email protected]','931 Thomas
Jefferson','Charlottesville'),
(4,'James','Madison','[email protected]','11350 Constitution
Hwy','Orange'),
(5,'James','Monroe','[email protected]','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,c.firstname, c.lastname, o.orderdate, o.amount
FROM customer c
INNER JOIN customerorder o
ON c.customer_id = o.customer_id;

Left join
--------
SELECT c.firstname, c.lastname, o.orderdate, o.amount
FROM customer c
LEFT JOIN customerorder o
ON c.customer_id = o.customer_id;

SELECT c.firstname, c.lastname, o.orderdate, o.amount


FROM customer c
LEFT JOIN customerorder o
ON c.customer_id = o.customer_id
WHERE o.orderdate IS NULL;
right join
----------
SELECT c.firstname, c.lastname, o.orderdate, o.amount
FROM customer c
RIGHT JOIN customerorder o
ON c.customer_id = o.customer_id;

You might also like