fbpx

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

In this article, you will learn about CROSS JOIN, INNER JOIN and Union language elements.

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.

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.

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. 

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.

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

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

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.

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.

Want to Learn More?

Be sure to download the RealPars app (iOS, Apple TV, Android, Android TV, firetv, Roku). 

When you download the app, you’ll be able to watch an entire free course on PLC Hardware. Just like this article, the lessons are all high-quality and also very easy-to-follow.  

By downloading the RealPars app, you can have access to a wealth of practical knowledge as an automation engineer right in your pocket and you will also receive new fresh out of the oven videos each and every week.

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

By Wally Gastreich

By Wally Gastreich

Automation Engineer

Posted on Mar 16, 2020

By Wally Gastreich

Automation Engineer

Posted on Mar 16, 2020

What is a Functional Design Specification (FDS)?

What is a Functional Design Specification (FDS)?

A Functional Design Specification also is known as FDS is a document that describes how a process or a control system will operate. Functional Design Specification does not contain any highly technical detail. Rather, it describes how the proposed system will operate,...

RealPars is the world's largest online learning platform for automation engineers.

Have questions? 

hello@realpars.com

+31 10 316 6400

Mon - Fri  8:30 am to 5:30 pm (CET)

Rotterdam Science Tower, Marconistraat 16,
3029AK Rotterdam, The Netherlands

COMPANY

About

Privacy

Cookies

LEARN

Courses

PLC Certificate

Blog

Search

Sign in

HELP

FAQ

Contact Us

© 2020 RealPras B.V. All rights reserved.

Created with coffee and tea in Rotterdam.