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

5 Actionable Tips for Getting a PLC Programming Job with NO Experience

5 Actionable Tips for Getting a PLC Programming Job with NO Experience

In this blog post, you’ll learn about the mindset that helped me getting a PLC programming job with NO experience. This is my personal experience as someone who searched for a job in this field and as an employer who reviews resumes and interviews candidates for a variety of projects. So let’s get started!

Manometer Explained | Working Principle

Manometer Explained | Working Principle

In this article, we're going to introduce you to the manometer which is one of the oldest pressure measurement devices still in use today. A manometer is one of the most accurate devices for measuring pressure in the lower ranges. Since manometers are so accurate,...

What is the Industrial Internet of Things (IIoT)?

What is the Industrial Internet of Things (IIoT)?

Automation and Internet Cloud Computing are evolving and merging into a space allowing for Artificial Intelligence (AI) programming to facilitate improvements in productivity and efficiency with many economic benefits.Industry 4.0Along with Internet Cloud computing...

Learn how to program PLCs, install and wire industrial devices, and at the same time purchase them online.

Have questions? 

[email protected]

+31 10 316 6400

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

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

COMPANY

About

Privacy

Cookies

LEARN

Courses

PLC Certificate

Blog

Search

Sign in

HELP

FAQ

Contact Us

© 2020 RealPars B.V. All rights reserved.

Created with coffee and tea in Rotterdam.