SQL Reference JOIN, UNION, SET Operations. This article is all about those.
Custom Names
SELECT ct.ID, ct.Name, ord.Name, ord.Amount
FROM customers AS ct, orders AS ord
WHERE ct.ID=ord.Customer_ID
ORDER BY ct.ID;
Types of Join
The following are the types of JOIN that can be used in MySQL:
– INNER JOIN
– LEFT JOIN
– RIGHT JOIN
INNER JOIN
INNER JOIN is equivalent to JOIN. It returns rows when there is a match between the tables.
SELECT column_name(s)
FROM table1 INNER JOIN table2
ON table1.column_name=table2.column_name;
LEFT JOIN
The LEFT JOIN returns all rows from the left table, even if there are no matches in the right table.
This means that if there are no matches for the ON clause in the table on the right, the join will still return the rows from the first table in the result.
SELECT table1.column1, table2.column2…
FROM table1 LEFT OUTER JOIN table2
ON table1.column_name = table2.column_name;
Note : The OUTER keyword is optional, and can be omitted.
RIGHT JOIN
The RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table.
SELECT table1.column1, table2.column2…
FROM table1 RIGHT OUTER JOIN table2
ON table1.column_name = table2.column_name;
Note : Again, the OUTER keyword is optional, and can be omitted.
Set Operation
Occasionally, you might need to combine data from multiple tables into one comprehensive dataset. This may be for tables with similar data within the same database or maybe there is a need to combine similar data across databases or even across servers.
To accomplish this, use the UNION and UNION ALL operators.
UNION combines multiple datasets into a single dataset, and removes any existing duplicates.
UNION ALL combines multiple datasets into one dataset, but does not remove duplicate rows.
Note : UNION ALL is faster than UNION, as it does not perform the duplicate removal operation over the data set.
UNION
The UNION operator is used to combine the result-sets of two or more SELECT statements.
All SELECT statements within the UNION must have the same number of columns. The columns must also have the same data types. Also, the columns in each SELECT statement must be in the same order.
The syntax of UNION is as follows:
SELECT column_name(s)
FROM table1
UNION
SELECT column_name(s) FROM table2;
TIP:If your columns don’t match exactly across all queries, you can use a NULL (or any other) value such as:
SELECT FirstName, LastName, Company FROM businessContacts
UNION
SELECT FirstName, LastName, NULL FROM otherContacts;
UNION ALL
UNION ALL selects all rows from each table and combines them into a single table.
The following SQL statement uses UNION ALL to select data from the First and Second tables:
SELECT ID, FirstName, LastName, City FROM First
UNION ALL
SELECT ID, FirstName, LastName, City FROM Second;
I hope you like this article ‘SQL Reference JOIN, UNION, SET Operations’ and understands most of it. Drop you commands and opinions below.