When you include multiple data sources in a query, you use
JOINS to limit the records that you want to see based on how the data sources
are related to each other. You must use JOINS to combine records from both data
sources, so that each pair of records from the sources becomes one record in
the query results. JOINS in MS Access is just the same as any other Relational
Database Management System (RDBMS) like MySQL
You can also join queries in the same way that you join
tables and can also join both tables and queries. Joins behave similarly to
query criteria in that they establish rules that the data must match before
they are included in the query operations. Unlike criteria, joins also specify
that each pair of rows that satisfy the join condition will be combined in the
record set to form a single row.
In the previous part (Part 3), I explained UNION query SQL in MS Access and MySQL in detail. Also see the full tutorial course content of this Microsoft Access Tutorial Series.
TYPES OF QUERY JOINS
There are many types of query joins, but I will discuss only
the five most common JOIN types. They include:
1. Inner Join
2. Left Outer Join
3. Right Inner Join
4. Full Outer Join
5. Cross Join
NOTE:
To create query joins in MS Access:
Create an empty SELECT query through the Query Design
option. Then switch the empty SELECT query over to SQL view where you can type
your SQL statements directly.
INNER JOIN:
Inner Join shows only rows where a common value exists in
both of the joined tables. They are the most common type join. When a query
with an inner join is run, only those rows where a common value exists in both
of the joined tables will be returned. For example, the tables below are used
for storing information of emergency patients and those of regular patience in
a Health Clinic. The first table is named emergency_patients
and the second, regular-patients.
Referring to the two tables above, if you want to show the
ID and HOME TOWN fields and return only the records of emergency patients that
are also regular patients, then your inner join query SQL would resemble this:
SELECT E.[ID],
R.[HOME TOWN] FROM [emergency_patients] AS E INNER JOIN [regular_patients] AS R
ON E.[ID] = R.[ID];
The query returns four records that appear in both tables as
shown below.
Also note how I aliased the table names. Note also, the ON clause used here in place of the WHERE clause, you have Join Queries.
LEFT OUTER JOIN:
When a query with a left outer join is run, it returns all
the records from the first (left) table and the corresponding records from the
second table that have a matching value in the join field of the first table.
For example, still referring to the above two tables, if you
want your query to show the ID and HOME TOWN fields and return all the records
of emergency patients, including the records of regular patients that are
emergency patients, then your left join SQL statement would resemble this:
SELECT E.[ID],
R.[HOME TOWN] FROM [emergency_patients] AS E LEFT OUTER JOIN [regular_patients]
AS R ON E.[ID] = R.[ID];
The query returns 6 records as shown below.
Two records returned in the screenshot above have empty data
under the HOME TOWN field because the emergency_patients
table don’t have the HOME TOWN field. So the query returned empty spaces.
RIGHT OUTER JOIN:
When a query with a right outer join is run, it returns all
the records from the second (right) table and the corresponding records from
the first table that have a matching value in the join field of the second
table.
For example, still referring to the above two tables, if you
want your query to show the ID and HOME TOWN fields and return all the records
or regular patients, then your SQL statement should resemble this:
SELECT E.[ID],
R.[HOME TOWN] FROM [emergency_patients] AS E RIGHT OUTER JOIN
[regular_patients] AS R ON E.[ID] = R.[ID];
The query returns 5 records as shown below.
The last returned record has empty data under the ID field
because the record was from the regular_patients
table and has no matching ID in the emergency_patients
table. So Access returned an empty value in that field for that particular
record.
FULL OUTER JOIN:
When a query with a full outer join is run, it shows all
rows from both tables regardless of whether they are present in the second
table. The SQL statement for a full outer join is database dependent. MS Access
does not clearly support full outer join, but you can use a UNION query that
combines the left and right outer join to perform it.
For example, still referring to the first two tables above,
if you want your query to show the ID and HOME TOWN fields and return the
records of all the patients of that clinic, that is both the emergency and
regular patients, then your SQL statement would resemble this:
SELECT E.[ID],
R.[HOME TOWN] FROM [emergency_patients] AS E LEFT OUTER JOIN [regular_patients]
AS R ON E.[ID] = R.[ID]
UNION
SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E RIGHT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID]
WHERE [E.ID] is NULL;
UNION
SELECT E.[ID], R.[HOME TOWN] FROM [emergency_patients] AS E RIGHT OUTER JOIN [regular_patients] AS R ON E.[ID] = R.[ID]
WHERE [E.ID] is NULL;
The query returns 7 records as shown below.
NOTE:
Do not use the ALL keyword
when using a UNION query to perform a full outer join.
CROSS JOIN:
Cross Join is also referred to as Cartesian Join. It mostly
returns undesired results. It is a join of tables or queries without specifying
the join fields or the join condition. When this happens, MS Access combines
every row from each table or query with the other rows of other tables or
queries involved.
A cross join is rarely the desired result, but an indication
that some join conditions are missing in the SQL statement.
An example of the SQL statement of a Cross Join between the
first two tables above is:
SELECT E.[ID], R.[ID],
R.[HOME TOWN] FROM [emergency_patients]
AS E JOIN [regular_patients] AS R;
Did you try running the above Cross Join SQL statement? What
was your result?
Of course an error message!
Now let me test your knowledge in this chapter.
PRACTICAL EXERCISES ON SQL
Try to practise these SQL real life exercises on your own.
Study the two tables shown below.
Use them to answer and solve the following problem:
1. Design the two tables shown above inside one MS Access database.
2. With reference to these tables, write an SQL statement to select the ID field from the customers table and an aliased calculated field that holds the total amount owed by each of the debtors in each case. The SQL statement should contain a query to return only the records of each of the categories of people from the above tables:
a. Only those that are both customers and debtors.
b. All customers including debtors that are also customers.
c. All debtors including customers that are also debtors.
d. All customers and all debtors.
This is the end of chapter 6 of this FREE online MS Access tutorial training series.
In the next chapter (chapter 7), I will explain and illustrate how to create different types of reports in MS Access and then How to Design the Switch board.
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 chapter (chapter 7), I will explain and illustrate how to create different types of reports in MS Access and then How to Design the Switch board.
With HTML, CSS, PHP In Dreamweaver CS5 And CS6, MySQL And MS Access 2013 And 2016 By David Max
Click Here to know more about the book.
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.
No comments:
Post a Comment
WHAT'S ON YOUR MIND?
WE LOVE TO HEAR FROM YOU!