SQL Step-by-step UNION and outer joins

UNION and external connections

Sometimes you might want to look at the results of multiple queries together and combine their output, and you can use the Union keyword. To merge the output of the following two queries: Display all buyer IDs and customers who already have orders, you can use the following statement:

SELECT Buyerid

From Antiqueowners

UNION

SELECT ownerID

From ORDERS;

Note here that the SQL requires that the list of select must match, which is listed in the data type match. In this example, Buyerid and ownerID are all the same data types, with the same interger (integral type). Also mentioned is that SQL but use Union for automatic copy exclusion. In a single query, you have to use distinct.

Outer join (outer joins) is usually used when join queries are joined, and rows are not included in the join, especially when the constant text "flags" is included. Now let's take a look at this query first:

SELECT ownerID, ' in both Orders & Antiques '

From ORDERS, antiques

WHERE ownerID = Buyerid

UNION

SELECT Buyerid, ' in antiques '

From Antiques

WHERE Buyerid not in

(SELECT ownerID

from ORDERS);

The first query makes a connection to list each of the two tables, and owener the ID by placing a tag line behind it. This union combines this list with the second list below. The second list lists the IDs that are not in the Orders table, resulting in a list of IDs outside the join query, which is listed by reference labels. This may be one of the easiest ways to generate this list.

This concept is useful for the state of the primary key in relation to the outer code, but some primary keys have a null external code value. For example, in a table, the primary key is salesperson, and in other tables the primary key is customers, and their salesperson are listed in the same row. However, if salesperson does not have customers, this person's name will not appear in the Customer table. If the list of all salespersons is to be displayed, then the external connection is necessary.