This post is the second in a series of posts which goes into far more detail on how to make the most of the SQL functionality in Mapinfo.
As we're previously seen a simple SQL query is comprised of a number of key elements, SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT. We have looked in detail at the SELECT section of a query and this post will look in far more detail at the FROM section of a query. Remember that a SQL query doesn't need to have to include all of these conditions but having a good understanding of all of them will allow you to make best use of SQL's powerful tools and save you time.
Put simply the FROM section of a SQL query is focused on indicating where the data that is being searched should be drawn from.
If all of the data that you are looking for is available in just one table then this will simply just be the name of the table that you need to search. So for a simple search it would be
SELECT column1
FROM table1
However as soon as you begin looking at searching more than one table this becomes far more complex. This requires you to JOIN the data of more than one table and explain how you want the tables to be JOINed.
The key to understanding JOIN is to take a picture of 2 overlapping circles, each of which represents a different table.
As we're previously seen a simple SQL query is comprised of a number of key elements, SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and LIMIT. We have looked in detail at the SELECT section of a query and this post will look in far more detail at the FROM section of a query. Remember that a SQL query doesn't need to have to include all of these conditions but having a good understanding of all of them will allow you to make best use of SQL's powerful tools and save you time.
Put simply the FROM section of a SQL query is focused on indicating where the data that is being searched should be drawn from.
If all of the data that you are looking for is available in just one table then this will simply just be the name of the table that you need to search. So for a simple search it would be
SELECT column1
FROM table1
However as soon as you begin looking at searching more than one table this becomes far more complex. This requires you to JOIN the data of more than one table and explain how you want the tables to be JOINed.
The key to understanding JOIN is to take a picture of 2 overlapping circles, each of which represents a different table.
Before we dive into explaining JOIN though it is important to explain two key principles:
1. What do the various sections of the Venn Diagram relate to? Which section am I looking for?
You must be clear about how the two tables relate to each other. So, for example, if one table A contains a list of names and addresses and another table B contains a list of cars, then the intersection of the two tables (area C ) would be those people in the database where we also know what car they own. Obviously table B less area C would be a list of cars where we don't know who owns them and equally table A less area C would be a list of people and their addresses who don't own a car / where we don't know that they own a car.
Querying the various different potential permutations is why we have a number of different JOINs that we can use.
2. How do the two tables relate to each other in the database?
You need to 'tell' the SQL query how the two tables relate to each other. The records will be joined as they exist in the same database but SQL will need to be told how. The majority of the time the primary key of table A will be a foreign key in table B, or vice-versa. If you do know understand what primary key and foreign key relates to I have included a brief paragraph at the end of this article to explain that.
This requirement means that all of the JOIN statement we will be looking at below are followed by syntax along the lines of "ON A.key=B.key". This tells the query which column of data that link the two tables. So usually how the primary key in table A appears as a foreign key in table B.
Obviously using the above construction there are, for two joined tables, five possibilities of how they can be combined.
1. FULL JOIN or FULL OUTER JOIN
This combines the two tables and a query will operate on all records. Where there is a record in table A which doesn't correspond with a record in table B the combined dataset will contain NULL for all entries coming from table A.
A pro-forma query showing this is:
SELECT <select_list>
FROM table A FULL OUTER JOIN table B
ON A.Key = B.Key
An obvious extension of this is if you wanted a search which showed only the unique records in table A and table B, and to exclude those which had corresponding entries then this could be extended to:
SELECT <select_list>
FROM table A FULL OUTER JOIN table B
ON A.Key = B.Key
WHERE table A.id is NULL or table B.id is NULL
In effect this would exclude area C in the diagram above.
2. INNER JOIN
This searches only those records that appear in both tables A and B. So if a record appears in table A but there is no corresponding record in table B, or vice-versa it will not be queried.
A pro-forma query showing this is:
SELECT <select_list>
FROM Table_A FULL OUTER JOIN Table_B
ON A.Key = B.Key
3. LEFT OUTER JOIN (AND RIGHT OUTER JOIN) - Inclusive
This searches a complete set of records from table A. Where there is matching data from table B that can be appended, if there is no matching data then it will show as null.
Left is used to refer to table A and is always used to refer to the first table referenced in the query. Correspondingly Right is used to refer to the second table referenced in the query. As such left join and right join are mirror images of each other. For MapInfo users this is probably a distinction they won't need to worry about.
A pro-forma query showing this is:
SELECT <select_list>
FROM table A LEFT JOIN table B
ON A.Key = B.Key
The mirror image query of this is:
SELECT <select_list>
FROM table B RIGHT JOIN table A
ON B.Key = A.Key
It is worth noting that commonly people want to produce a sert of records only in Table A, excluding a series of records that they don't want from the right side using a where clause.
So for example if you wanted the records from table A where there is a corresponding record in table B then your would search as follows:
SELECT <select_list>
FROM table A LEFT JOIN table B
ON A.Key = B.Key
WHERE table B is NOT NULL
APPENDIX - PRIMARY & FOREIGN KEYS
This appendix is intended as a brief introduciton to primary and foreign keys, at the level at which a MapInfo user would find it useful. There is more to keys than this can hope to cover. If you would like a more detailed explanation of this please visit Wikipedia.
Primary key:
Each database table must have a primary key to allow it to identify each row. The primary key can be an existing entry. For example if your table contains car number plates, National Insurance numbers or a unique customer number for a customer that can be used, provided you know that the entries for the cars or individuals will never be repated. However more usually you will set up a table so that the database will automatically insert a unique identifier as the first item in each row of data.
Foreign key:
As discussed above each row of data must contain a unique identifier. However, the unique identifier in, for example table A, is also quite likely to appear in table B, although not as it's primary key. This is how you ensure that you can link the various pieces of data between the two tables. When a column of data that is the primary key in one database table appears in another table in that database it is known as a foreign key. This obviously only applies when speaking about the second table.
1. What do the various sections of the Venn Diagram relate to? Which section am I looking for?
You must be clear about how the two tables relate to each other. So, for example, if one table A contains a list of names and addresses and another table B contains a list of cars, then the intersection of the two tables (area C ) would be those people in the database where we also know what car they own. Obviously table B less area C would be a list of cars where we don't know who owns them and equally table A less area C would be a list of people and their addresses who don't own a car / where we don't know that they own a car.
Querying the various different potential permutations is why we have a number of different JOINs that we can use.
2. How do the two tables relate to each other in the database?
You need to 'tell' the SQL query how the two tables relate to each other. The records will be joined as they exist in the same database but SQL will need to be told how. The majority of the time the primary key of table A will be a foreign key in table B, or vice-versa. If you do know understand what primary key and foreign key relates to I have included a brief paragraph at the end of this article to explain that.
This requirement means that all of the JOIN statement we will be looking at below are followed by syntax along the lines of "ON A.key=B.key". This tells the query which column of data that link the two tables. So usually how the primary key in table A appears as a foreign key in table B.
Obviously using the above construction there are, for two joined tables, five possibilities of how they can be combined.
1. FULL JOIN or FULL OUTER JOIN
This combines the two tables and a query will operate on all records. Where there is a record in table A which doesn't correspond with a record in table B the combined dataset will contain NULL for all entries coming from table A.
A pro-forma query showing this is:
SELECT <select_list>
FROM table A FULL OUTER JOIN table B
ON A.Key = B.Key
An obvious extension of this is if you wanted a search which showed only the unique records in table A and table B, and to exclude those which had corresponding entries then this could be extended to:
SELECT <select_list>
FROM table A FULL OUTER JOIN table B
ON A.Key = B.Key
WHERE table A.id is NULL or table B.id is NULL
In effect this would exclude area C in the diagram above.
2. INNER JOIN
This searches only those records that appear in both tables A and B. So if a record appears in table A but there is no corresponding record in table B, or vice-versa it will not be queried.
A pro-forma query showing this is:
SELECT <select_list>
FROM Table_A FULL OUTER JOIN Table_B
ON A.Key = B.Key
3. LEFT OUTER JOIN (AND RIGHT OUTER JOIN) - Inclusive
This searches a complete set of records from table A. Where there is matching data from table B that can be appended, if there is no matching data then it will show as null.
Left is used to refer to table A and is always used to refer to the first table referenced in the query. Correspondingly Right is used to refer to the second table referenced in the query. As such left join and right join are mirror images of each other. For MapInfo users this is probably a distinction they won't need to worry about.
A pro-forma query showing this is:
SELECT <select_list>
FROM table A LEFT JOIN table B
ON A.Key = B.Key
The mirror image query of this is:
SELECT <select_list>
FROM table B RIGHT JOIN table A
ON B.Key = A.Key
It is worth noting that commonly people want to produce a sert of records only in Table A, excluding a series of records that they don't want from the right side using a where clause.
So for example if you wanted the records from table A where there is a corresponding record in table B then your would search as follows:
SELECT <select_list>
FROM table A LEFT JOIN table B
ON A.Key = B.Key
WHERE table B is NOT NULL
APPENDIX - PRIMARY & FOREIGN KEYS
This appendix is intended as a brief introduciton to primary and foreign keys, at the level at which a MapInfo user would find it useful. There is more to keys than this can hope to cover. If you would like a more detailed explanation of this please visit Wikipedia.
Primary key:
Each database table must have a primary key to allow it to identify each row. The primary key can be an existing entry. For example if your table contains car number plates, National Insurance numbers or a unique customer number for a customer that can be used, provided you know that the entries for the cars or individuals will never be repated. However more usually you will set up a table so that the database will automatically insert a unique identifier as the first item in each row of data.
Foreign key:
As discussed above each row of data must contain a unique identifier. However, the unique identifier in, for example table A, is also quite likely to appear in table B, although not as it's primary key. This is how you ensure that you can link the various pieces of data between the two tables. When a column of data that is the primary key in one database table appears in another table in that database it is known as a foreign key. This obviously only applies when speaking about the second table.
RSS Feed
