In this video,
“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 video,
“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 “JOIN” statement.
Ok, let’s first join the tables,
using the “CROSS JOIN” Statement that I’m writing into the “SQL Query” tab,
based on the column “ID” in both tables.
Let’s try this statement.
I 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.
At RealPars, we love helping you learn.
So, if you enjoy this video as much as we enjoyed making it,
Click the like button.
Subscribe and click the bell
and you’ll receive notifications of new RealPars videos.
So, you’ll never miss another one!
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 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.
I will start by writing the "INNER JOIN" statement,
which I just explained, into the "SQL Query" tab.
Let’s try this statement.
I press the “Execute Query” button to run the “INNER JOIN” statement.
The result will display 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”.
The “UNION” operator allows you to combine two or more result sets of queries
into a single result set.
The 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.
These “Drop Table” statements will delete the earlier two tables, “t1” and “t2”,
And these “Create Table” statements will create two more tables, “t1” and “t2”,
And these “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.
I press the “Execute Query” button to run These SQL statements.
I will refresh the “Navigator” Panel by selecting the “Refresh” button.
I then expand the items of “t1” and “t2” tables
to see the “ID” column I created for each of these tables.
I 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”.
I do the same procedure, as I did for the “t1” table,
to see the records of the “t2” table.
Now I return to the “SQL Query” tab
and start writing 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.
You can find the link to this website in the description below.
This concludes the video,
“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.
If you enjoyed this video, please press the like button.
Please check back with us soon for more automation control topics.