SQL Staring Blocks - A basic look at punctuation and JOIN

SQL uses some syntax that can be confusing at first glance.  Let's look at this really basic example:

SELECT table1.`first name`, table2.city
FROM table1 LEFT JOIN table2
ON (table1.id = table2.user_id)
WHERE table2.city = "My Home Town";

Here you can see as well as the key words that are in block capitals there is some punctuation throughout the statement.  This has a function.  Starting with the comma, that's just what you would expect, it separates items in a list.  Next the full stop (or period if you prefer) this is slightly different, where you see oneThing.secondThing this is telling the database that the you want to access the sub-component of oneThing that is called secondThing.  What I mean by that is easier taken in context of your basic windows file system layout:  You have your My Documents folder, within that you have a folder for all your text files call Text Docs and within that you would have a file called MyFile.txt.  For the file system to access this it runs down through the folders until it gets to the file and would end up looking at My Documents\Text Docs\MyFile.txt.  The way a database system looks at it is just the same, only the \ are replaced by .
So if we change it up so that what we have is a database called My Documents, a table within that database called Text Docs and a column named MyFile in that table that we want to query the information on we would access that bit of information by using

SELECT `My Documents`.`Text Docs`.MyFile

You will only need to put the name of the database that the table is in into your query if it is a different database from the one that you are running the query in and if you also need to access information from the database that you are currently in as well.  If you only need to use a single database always run the query from within that database - to do this use the USE command i.e.

USE databaseOne;

The next thing that you will notice regarding the use of punctuation marks is the backtick around some of the names of things ( ` ) This is NOT a single quote and does not do what a single quote does.  A single quote in SQL tells the database that you have a string of characters which, as far as it is concerned, are not related to anything in the makeup of the database.  The backtick however tells the database that, while the contents are still a string, the contents have a relation to something that makes up the database structure, such as a table name, a field name or a database name and are required if any of these names have a space in them.
-This is only a very basic explanation of the actual functionality of backticks so as to keep it as clear as I can.

Using backticks also lets you use queries on fields that you have named with one of the "Reserved Words" in your database.  You should always take care to avoid using reserved words when naming tables and columns in your database, but sometimes one creeps in.  If this happens you can use backticks around the name to tell the database not to treat the word as it normally would.

Something else that can be quite confusing the the use of JOIN's in SQL syntax.  JOIN's always go in the FROM part of your query and let you perform queries on multiple tables at the same time.  there are a couple of different joins and the exact syntax for how to call each one depends on which database you are running your query on.  For basic MySQL JOIN's you would use

FROM table1 {XXXX} JOIN table2 ON (table1.commonField = table2.matchingField)

You would replace the {XXXX} with either INNER, LEFT or RIGHT depending on how you want to join your two tables.  You must also join the tables on common fields that have a value that can be matched on each side of the join (or else there is no point in joining them).

INNER JOIN is used to create a 1-1 relationship between each record in each table.  If there is not a matching record in both the common and matched fields of table1 and table2, the record will be dropped from the dataset that your query returns.  This is usefull when you only want to find matching records between two tables:

SELECT table1.operator, table1.time, table2.location, table2.customerName
FROM table1 INNER JOIN table2 ON
(table1.jobID = table2.jobRefID)
WHERE table1.jobID = 100023

LEFT JOIN creates a 1-MANY relationship between the tables using the table on the left of the = in the ON clause as the "main" table and returning every record from the table on the right for every match that is found between the two tables, this is handy when you want you have what I call a "master and detail" table setup, where you have a "master" table that has limited records, such as product groups, and a "detail" table that has a lot of lines, such as a product description table, now say you want all the products and their descriptions that match a specific product group:

SELECT detail.productName, detail.productDescription
FROM master LEFT JOIN detail ON
(master.ID = detail.groupID)
WHERE master.ID = 3

RIGHT JOIN is the exact reverse of LEFT JOIN, creating a MANY-1 relationship.  RIGHT is generaly not used as much as LEFT because it's normaly easier for people to visualise the gathering of data using the 1-MANY model over the MANY-1.