>> Suppose I have TABLE Customers and TABLE Orders. The relationship is that 1 customer can have many or no orders. <<
The conventional SQL forms for over 30 years has been that you post some data and some DDL when asking for help.
Is this what you might have posted if you had been polite? Please notice the use of references to relate with tables together. That's what the R in RDBMS stands for 🙂 . Notice that the tables have keys; this is not an option. Here is a skeleton schema
CREATE TABLE Customers
(customer_id CHAR(16) NOT NULL PRIMARY KEY, -- credit card number as key!
CREATE TABLE Orders
(order_id CHAR(20) NOT NULL PRIMARY KEY,
customer_id CHAR(16) NOT NULL
PRIMARY KEY (order_id, customer_id),
CREATE TABLE Orders_Details
(order_id CHAR(20) NOT NULL REFERENCES Orders,
item_gtin CHAR(15) NOT NULL, --- look up GTIN if you don't know what it is
PRIMARY KEY (order_id, item_gtin),
>> I have to use UNION, to retrieve customer records [sic: rows are nothing like records] that does not have any orders. I can get the answer via LEFT OUTER JOIN, but just can't seem to figure how to do it with UNION. <<
Is this some sort of weird class problem? This is a needless constraint. SQL has said oriented operators that can be quite fast, as well as very compact.
(SELECT customer_id FROM Customers
SELECT customer_id FROM Orders) AS X (virgin_customer_id)
You might want to look up the UNION [ALL], INTERSECT [ALL], and EXCEPT [ALL] operators
Please post DDL and follow ANSI/ISO standards when asking for help.