SQL Server 2019 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Create the Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50),
    ContactEmail VARCHAR(100)
);

-- Insert data into the Customers table
INSERT INTO Customers (CustomerID, CustomerName, ContactEmail)
VALUES
    (1, 'John Doe', 'john.doe@example.com'),
    (2, 'Jane Smith', 'jane.smith@example.com'),
    (3, 'Michael Johnson', 'michael.johnson@example.com'),
    (4, 'Sarah Williams', 'sarah.williams@example.com'), -- Customer with no order
    (5, 'Tony Chiu', 'tony.chiu@example.com'); -- Customer with no order

-- Create the Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10, 2)
);

-- Insert data into the Orders table
INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
VALUES
    (101, 1, '2023-08-01', 100.00),
    (102, 2, '2023-08-02', 50.00),
    (103, 1, '2023-08-02', 75.00),
    (104, 3, '2023-08-03', 200.00),
    (105, 9, '2023-08-04', 150.00); -- Order with an invalid CustomerID
10 rows affected

SELECT *
FROM Customers


CustomerID
CustomerName
ContactEmail


1
John Doe
john.doe@example.com

2
Jane Smith
jane.smith@example.com

3
Michael Johnson
michael.johnson@example.com

4
Sarah Williams
sarah.williams@example.com

5
Tony Chiu
tony.chiu@example.com


SELECT *
FROM Orders


OrderID
CustomerID
OrderDate
TotalAmount


101
1
2023-08-01
100.00

102
2
2023-08-02
50.00

103
1
2023-08-02
75.00

104
3
2023-08-03
200.00

105
9
2023-08-04
150.00


SELECT C.CustomerID
	,C.CustomerName
	,C.ContactEmail
	,O.OrderID
	,O.OrderDate
	,O.TotalAmount
FROM Customers C
FULL JOIN Orders O ON C.CustomerID = O.CustomerID;


CustomerID
CustomerName
ContactEmail
OrderID
OrderDate
TotalAmount


1
John Doe
john.doe@example.com
101
2023-08-01
100.00

1
John Doe
john.doe@example.com
103
2023-08-02
75.00

2
Jane Smith
jane.smith@example.com
102
2023-08-02
50.00

3
Michael Johnson
michael.johnson@example.com
104
2023-08-03
200.00

4
Sarah Williams
sarah.williams@example.com
null
null
null

5
Tony Chiu
tony.chiu@example.com
null
null
null

null
null
null
105
2023-08-04
150.00


SELECT C.CustomerID
	,C.CustomerName
	,C.ContactEmail
	,O.OrderID
	,O.OrderDate
	,O.TotalAmount
FROM Customers C
FULL JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE C.CustomerID IS NULL
	OR O.OrderID IS NULL


CustomerID
CustomerName
ContactEmail
OrderID
OrderDate
TotalAmount


4
Sarah Williams
sarah.williams@example.com
null
null
null

5
Tony Chiu
tony.chiu@example.com
null
null
null

null
null
null
105
2023-08-04
150.00

CustomerID	CustomerName	ContactEmail
1	John Doe	john.doe@example.com
2	Jane Smith	jane.smith@example.com
3	Michael Johnson	michael.johnson@example.com
4	Sarah Williams	sarah.williams@example.com
5	Tony Chiu	tony.chiu@example.com

OrderID	CustomerID	OrderDate	TotalAmount
101	1	2023-08-01	100.00
102	2	2023-08-02	50.00
103	1	2023-08-02	75.00
104	3	2023-08-03	200.00
105	9	2023-08-04	150.00

CustomerID	CustomerName	ContactEmail	OrderID	OrderDate	TotalAmount
4	Sarah Williams	sarah.williams@example.com	null	null	null
5	Tony Chiu	tony.chiu@example.com	null	null	null
null	null	null	105	2023-08-04	150.00