An understanding of SQL is not necessary to build database applications in the Access
development environment. An understanding of SQL is beneficial, however, as queries
and applications become more complex. JOIN is a very powerful extension to define a
syntax for retrieval from multiple tables to resolve some very complex queries.
The simple logic behind SELECT is:
SELECT what FROM whatobject
or
SELECT what FROM whatobject WHERE expression
The only required key for the select is the FROM key, but queries can become much
more involved, even to the point that a single SQL command might almost equate to a
program. The “what” in the SELECT logic refers to the field names or possibly some
aggregate or derived value. The “whatobject” refers to tables. The “expression”
qualifies the records of interest. An object reference in the SELECT statement often
needs qualification, because names are only unique within a table when multiple tables
are used. The “.” is used to qualify references by concatenating the table name,
followed by a “.”, followed by the field name.
AS is used in several components of the SELECT statement to create an alias name
that is more meaningful or possibly shorter to simplify the command understandability
and programmability. An alias can be defined for fields and tables, typically in the
“what” for fields and the “whatobject” for tables. The scope of the alias is only within
the SQL command. Syntactically, the AS keyword follows an object name, and the
new alias name follows the AS. In the remainder of the SQL command, the alias can be
substituted for the original object name.
DISTINCT is included in the “what” string to convey the request that any duplicate
occurrences of an object be displayed or reported only once. The COUNT() function is
also often part of the “what” string for retried record counts. Using these two together,
ad hoc summary information is easily retrieved.
There are many basic components that can be added to extend and enhance the query
power of SELECT, JOIN being one that can be very simple or very complex. It
identifies multiple table sources and how their data can be merged for new table views.
Table design is very important when using JOIN. The table key selection is important.
A table must contain only information about that table entity. JOIN will bring together
the detail information. Successful table joining can be achieved by equating table keys
with the WHERE extension. This merging, however, will check every row in one table
with every row of the other table (Cartesian product of two tables), and usually is not
the desired result. The JOIN keyword following the FROM tables is more generic.
The JOIN keyword is part of “whatobject” in SELECT. If the two tables are being
joined using fields with the same name and the join condition is equal, then the
following simplified syntax can be used for the “whatobject” value in SELECT:
“whatobject” = tableobject1 JOIN tableobject2 USING ( join_field_name )
(Multiple keys in USING are separated by “,”)
If the joined fields have different names or the join condition is not equality, then the
following syntax can be used:
“whatobject” = tableobject1 JOIN tableobject2
ON tableobject1.name1 = tableobject2.name2
SQL: has defined several types JOIN operations; INNER JOIN is the default if the type
is not specified.
INNER JOIN – Only records in left table and right table with matching keys
LEFT OUTER JOIN – All records of left table,
with info from right table only for those with matching keys
RIGHT OUTER JOIN – All records of right table,
With info from left table only for those with matching keys
FULL OUTER JOIN – All records of both tables,
with info from both table only for those with matching keys,
unmatched keys added from each, but with NULL for merged fields
SELF JOIN – join between two fields of the same table
Records with no matching keys for OUTER JOINS receive NULL values for the
merged fields. OUTER JOINs differ from the INNER JOIN only when the minimum
cardinality of a relation is 0.
Welcome to Legit Writing
