Practical Application Of Sql In Ms Access & Mysql - Union & Join Queries

I have been so theoretical since the introduction of this SQL chapter for MS Access and MySQL Databases. Now let me show you the real life or practical, real life and business applications of SQL (Structured Query Language) that I have been explaining. I will apply SQL to build these two types of advanced query in MS Access and MySQL databases, namely:

1. THE UNION QUERY

2. THE QUERY JOIN

THE UNION QUERY:

This just like the table relationship/join I explained in chapter 2. This query lets you combine the results returned by two SELECT statements or queries and displays them as one result. In table join, MS Access does all the underground work for you. All you just did then was to click and Access generates the SQL statements without your knowledge. But now, you will generate everything from the scratch. That is what makes it an advanced query.

This is part 3 of chapter 6 of the Free Online Access Tutorial Series at Microsoft Tutorial website. In this part, I will explicitly explain the real life or business application of SQL in Relational Database Management Systems (RDBMS) software like Microsoft Access 2016, 2013 and other lower versions.

In the previous part (Part 2), I explained all SQL clauses and Keywords in MS Access and MySQL in detail. Also see the full tutorial course content of this Microsoft Access Tutorial Series..

NOTES:

The UNION query joins two or more SELECT queries together, also, the SELECT statements that you combine in a union query must have the same number of output fields in the same order and with the same or compatible data types.

Also note that the Text data type and the Number data type are compatible for the purpose of union query.

You have to switch to the SQL view in MS Access to create a union query because a union query is SQL –specific.

The basic SQL syntax for a union query that combines two SELECT query is:

SELECT [field_1], [field_2],… FROM [table_1], [Table_2],…

UNION ALL

SELECT [field_a], [field_b],… FROM [table_a], [Table_b],…

NOTE:

The ALL keyword is optional. When you use the ALL keyword, duplicate rows are not removed from the combined set that is produced by the UNION query. You can also use it when you are sure that the SELECT statements will not produce any duplicate rows or if you want to see duplicate rows.

The UNION keyword combines the results returned by two SELECT statements or queries and displays them as one result.

HOW TO CREATE A UNION QUERY IN MS ACCESS

There are two basic methods of creating UNION queries in Microsoft Access:

1. Combining two SELECT queries previously created in Query Design view

2. Creating the UNION query directly in SQL view

COMBINING TWO SELECT QUERIES PREVIOUSLY CREATED IN QUERY DESIGN VIEW

This method becomes the alternative if you have previously created the two SELECT queries and then wish to combine or merge their results.

For example, as an Administrative Head of a University, you might want to merge or join two queries named old students record and new students record to form one query. Since you already have the two queries available, you will use this alternative to create the JOIN query.

To Combine Two Previously Created SELECT Queries Using the UNION Keyword:

1. Open the first SELECT query in SQL view and copy the SQL statement. Referring to the above example, I will first open the new students records SELECT query in SQL view and copy the SQL statement as shown below.

2. Click Query Design located in the Queries group under the Create tab.

3. This prompts the Show Table dialogue box. Close the dialogue box without selecting any query by clicking the Close button.

4. Click the UNION icon located in the Query Type group under the Design tab as shown below.

5. From the screenshot above, you will notice that the Query Design view option is no more available when you click the View drop down arrow. You only see two options: the Datasheet View and the SQL view. Also, the SQL view object tab is displayed but empty.

6. Now, paste the initially copied SQL statement for the first SELECT query into the SQL view object window of the UNION query as shown below. Delete the semi-colon at the end of the SELECT statement and press the Enter key to move the cursor down to a new line as shown below.

7. Type UNION. Optionally, you can type a space, followed by the ALL keyword. Then press the Enter key again to move your cursor to another new line.

8. Open the second SELECT query in SQL view and copy the SQL statement. So referring to the above example, I will open the old students records SELECT query in SQL view and copy the SQL statement.

9. With your cursor at the new line of in the SQL view object tab of the UNION query, paste the copy of the SQL statement of the second select query as shown below.

NOTE:

To add an additional SELECT query statement to the above UNION query, repeat steps 7 to 9 above.

10. Click the Run! icon located  beside the View drop down menu in the Results group under the Design tab. The result of your query appears in Datasheet view. Save the UNION query with a unique name. The result of the above example is shown below.

CREATING THE UNION QUERY DIRECTLY IN SQL VIEW

This method is the best alternative if you have not previously created the two SELECT queries. So you now type the whole SQL statement for the UNION query directly in the SQL view without having to copy and paste as you did in the first alternative above.

To Create the UNION Query Directly in SQL View:

1. Click Query Design located in the Queries group under the Create tab.

2. This prompts the Show Table dialogue box. Close the dialogue box without selecting any query by clicking the Close button.

3. Click the UNION icon located in the Query Type group under the Design tab. You will notice that the Query Design view option is no more available when you click the View drop down arrow. You only see two options: the Datasheet View and the SQL view. Also, the SQL view object tab is displayed but empty.

4. In the SQL view object window, type SELECT, followed by a list of the fields from the first table or set of tables that contain fields you want to include in the UNION query. Use a comma and a pace to separate the field names.

Your SELECT clause should take this format:

SELECT table1.[field1], table1.[fied2],…, table2.[field1], table2.[field2],…

Then press the Enter key when you are through to create a new line.

5. Type FROM, followed by the names of the tables that contain the fields listed in the preceding SELECT clause, separating the table names with a comma and a space. 

Your WHRE clause should follow this format:

FROM [table1], [table2],…

Then press the Enter key to create a new line. 

6, To specify a criterion for a field from one of the tables listed in the preceding FROM clause, type WHERE, followed by the field name, a comparison operator (usually an equality sign (=)) and the criterion. You can add additional criteria to the end of the WHERE clause by using the AND or the OR keyword. 

Your WHERE clause should follow this format:

WHERE table1.[field1] = ‘field data 1’ AND table2.[field1] = ‘field data 2’

If you specified criteria, press the Enter key to create a new line. 

With this, you have succeeded in creating the first SELECT query statement for the JOIN query. But don’t put a semi-colon at the end of this first SELECT query statement. You will put at the end of the second SELECT query statement (or the last SELECT query statement if you are creating more than two SELECT queries).

7. Type UNION. If you do not want to the query to remove duplicate rows from the output, type a space, followed by the ALL keyword. Then press the Enter key to create a new space.

8. Now you will start creating the second SELECT query statement for the JOIN query. Repeat steps 4 to 6. 

9. To include additional SELECT query statement in the JOIN query, type UNION. If you do not want to the query to remove duplicate rows from the output, type a space, followed by the ALL keyword. Then press the Enter key to create a new space. Then repeat steps 4 to 6.

10. Type a semi-colon to indicate the end of your query.

11. Click the Run! icon located  beside the View drop down menu in the Results group under the Design tab. The result of your query appears in Datasheet view. Save the UNION query with a unique name.

This is the end of part 3 of chapter 6 of this FREE online MS Access tutorial training series. 

Recommended MS Access Textbook

Get this book (Kindle format): Designing Professional Database Management Systems Using Microsoft Access 2013 and 2016 and MySQL.

Recommended Textbook

Learn Dynamic Web Development And DBMS:

With HTML, CSS, PHP In Dreamweaver CS5 And CS6, MySQL And MS Access 2013 And 2016 By David Max

Click To Buy This Book Now

Click Here to know more about the book.

In the next part (part 4), I will explain and illustrate the how to create JOIN queries in MS Access.

Was this tutorial helpful to you?

Inform your friends about this post by clicking the share button below. Comment below if you are hooked up along the installation process.

Also click Here to subscribe for free so that you will get our latest game updates in your email.