SQL Cross Join, Inner Join, and Union Clause (Part 8 of 8)

In this article, What are the SQL Cross Join, Inner Join and Union Clause statement language elements, you will learn about some of the advanced SQL statements and clause commands for CROSS JOIN, INNER JOIN and Union language elements.

In the previous article, “What is the SQL Create Table Clause Statement Language Element?”, we used the Create Table statements to create new tables, t1 and t2, in the RealPars sample database.

CREATE TABLE t1 (ID INT PRIMARY KEY,Pattern VARCHAR(50) NOT NULL);

view rawCREATE TABLE Example .sql hosted with ❤ by GitHub

CREATE TABLE t2 (ID VARCHAR(50) PRIMARY KEY,Pattern VARCHAR(50) NOT NULL);

view rawCREATE TABLE Examples .sql hosted with ❤ by GitHub

INSERT INTO t1 (ID, Pattern)VALUES (1, 'Divot'), (2, 'Brick'), (3, 'Grid');

view rawINSERT INTO .sql hosted with ❤ by GitHub

INSERT INTO t2 (ID, Pattern)VALUES ('A', 'Brick'), ('B', 'Grid'), ('C', 'Diamond');

view rawSQL INSERT INTO .sql hosted with ❤ by GitHub

Now we join these two tables, using the SQL JOIN statement.

CROSS JOIN Statement

OK, let’s first join the tables, using the CROSS JOIN Statement based on the column ID in both tables.

Let’s try this statement. Press the Execute SQL Query button to run the Cross Join statement. The result will be displayed in the Output Panel.

SELECT t1.ID, t2.IDFROM t1 CROSS JOIN t2;

view rawCROSS JOIN Example.sql hosted with ❤ by GitHub

As you can see, each row in the t1 table with column ID combines with rows in the t2 table with column ID to form the Cartesian product result.

INNER JOIN Statement

Join-predicate

To form an INNER JOIN, you will need a condition that is known as a join-predicate. An INNER JOIN requires rows in the two joined tables to have matching column values.

The INNER JOIN creates the result set by combining column values of two joined tables based on the join-predicate.

This is another example of the SQL statement, used to join the t1 and t2 tables. The INNER JOIN compares each row in the first table with each row in the second table to find pairs of rows that satisfy the join-predicate.

Whenever the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of these two tables are included in the result set.

In this statement, the t1.pattern = t2.pattern expression is the join-predicate.

Let’s try this statement.

SELECT t1.ID, t2.IDFROM t1 INNER JOIN t2ON t1.Pattern = t2.Pattern;

view rawINNER JOIN Example.sql hosted with ❤ by GitHub

Press the Execute Query button to run the INNER JOIN statement.

The result will be displayed the records with ID columns, where t1.pattern equals to t2.pattern.

When we review the contents of both tables t1 and t2, having records where the column Pattern data are equal, we see t1 and t2 IDs are respectfully 2-A  and 3-B.

UNION Operator

The UNION operator allows you to combine two or more result sets of queries into a single result-set. This statement will eliminate duplicate rows. Our final result-set which is created by the Union statement is shown here.

Let’s create new tables and perform a UNION statement and review the result.

Drop Table Statements

The Drop Table statements will delete the earlier two tables, t1 and t2.

DROP TABLE IF EXISTS t1;DROP TABLE IF EXISTS t2;

view rawDROP TABLE Example.sql hosted with ❤ by GitHub

And the Create Table statements will create two more tables, t1 and t2.

CREATE TABLE t1 (ID INT PRIMARY KEY);CREATE TABLE t2 (ID INT PRIMARY KEY);

view rawCREATE TABLE Ex.sql hosted with ❤ by GitHub

And the INSERT INTO statements will add a few values to column ID in both tables, t1 and t2.

INSERT INTO t1 VALUES (1), (2), (3);INSERT INTO t2 VALUES (2), (3), (4);

view rawINSERT INTO Example.sql hosted with ❤ by GitHub

OK, let’s run these statements in MySQL Workbench. Press the Execute Query button to run These SQL statements.

Refresh the Navigator Panel by selecting the Refresh button.

Then expand the items of t1 and t2 tables to see the ID column I created for each of these tables.

Right-click over the t1 table item in the navigator panel and choose Select Rows – Limit 1000.

By doing this, the records of the t1 table will be displayed in the Output Panel.

Do the same procedure, as you did for the t1 table, to see the records of the t2 table.

Now return to the SQL Query tab and write the Union statement to combine t1 and t2 tables into a single result-set.

SELECT ID FROM t1UNIONSELECT ID FROM t2;

view rawSQL UNION Example.sql hosted with ❤ by GitHub

Let’s try this statement by pressing the Execute Query button.

In the Output Panel, the result displays only records having column ID, that are distinct between the two tables.

t1 table records and t2 table records have IDs 2 and 3 that are not distinct so the result displays a record-set displaying only 4 of the total 6 records.

To learn more about MySQL and additional SQL statements, we encourage you to visit the MySQL website.

This concludes the article, What are the SQL Cross Join, Inner Join, and Union Clause statement language elements.

I hope you have enjoyed learning about Structured Query Language.

If you would like to get additional training on a similar subject please let us know in the comment section.

Check back with us soon for more automation control topics.

Got a friend, client, or colleague who could use some of this information? Please share this article.

The RealPars Team

Lessons in SQL Series of Articles

How to Install MySQL and Import a Sample Database (Part 1 of 8)

What are SQL Programming Basics? (Part 2 of 8)

What are SQL Where and Like Statements Basics? (Part 3 of 8)

What are the Order by Clause and Expression Statement Language Elements? (Part 4 of 8)

What are the Inner Join, And/or, Having and Between Clause Statement Language Elements? (Part 5 of 8)

What are the Subquery and Exists Clause statement language elements? (Part 6 of 8)

What is the SQL Create Table Clause Statement Language Element? (Part 7 of 8)

▶ What are the SQL Cross Join, Inner Join, and Union Clause statement language elements? (Part 8 of 8)

‍