SQL Select Statements Kept Simple
February 15, 2008 – 3:12 pm
This is a quick article on writing simple and complex select SQL statements. This primer can be used for mySQL and many other database systems. Please keep in mind this is a primer, please research and read other materials to enhance your knowledge.
For those of you who are wondering, mySQL is used in browser based game design quite often.
Select Statement
The select statement is used to get information from the database. It consists of 3 parts. The Fields the tables and the where clause.
SELECT [TABLE].[FIELDS]
FROM [TABLE]
WHERE [TABLE].[FIELD] = [VALUE]
select user.email
from user
where user.username = ‘mobeamer’
- Fields - Can contain a list of fields that you want to select.
- Table - will contain one, possibly more tables that hold the fields
- Where - This will allow you to restrict the information you receive.
Notice that I surrounded mobeamer with quotes, this is needed for strings and is good practice for other data types. A good rule of thumb is when in doubt add quotes.
Notice that I fully qualified the fields by putting the table name in front of the field name. This is not needed when writing a simple select but it is considered good form AND it will come in handy when you decide to “upgrade” your SQL statement (see joins).
Inner Join
An inner join will allow you to pull information from multiple tables with one query. The syntax for an inner join is as follows:
SELECT [TABLE].[FIELDS]
FROM [TABLE]
INNER JOIN [TABLE] ON [TABLE].[FIELD] = [TABLE].[FIELD]
WHERE [TABLE].[FIELD] = [VALUE]
Let’s say you have a user table which contains all the player’s information. You also have a units table that contains all the units that a player can have. You need a select statement which will get the player’s name and the unit’s name. In this instance my unit table has a column called ownerID which holds the userID of the owner.
select user.username, unit.unitname
from user
inner join unit on unit.ownerID = user.userID
where user.username=’mobeamer’
Another example:
select user.username, unit.unitname
from user
inner join unit on unit.ownerID = user.userID
where unit.class = ‘Warrior’
In most cases your joining field will be named the same in both tables, but I wanted to show how this was not necessary.
Notice, that you must fully qualify fields that exists in both tables. You should be aware when using an inner join, as in the first example, if the user does NOT have any units they will NOT appear in the result set.
Be very careful with inner joins as they ALWAYS restrict the result set. (See outer joins)
Outer Join
An outer join works in the same fashion as an inner join with one exception. The join will NOT restrict the returned set For example, in the example above, a player may not have a unit. In this case, an inner join would not pick up that player’s name. An outer join on the other hand would pickup this player.
select user.username, unit.unitname
from user
outer join unit on unit.ownerID = user.userID
where user.username=’mobeamer’
This will get all user, regardless of how many units they have.
Notice, the first table in the select statment begins the result set. Every outer join from there on can only add rows or columns to the recordset.
A good rule of thumb is to always use an outer join as you will never lose data with an outer join.
How I do It
This is how I write a complicated sql statement, this may not be best practices but I think it may add some context.
I wanted to create a page, which displays a player’s profile. I knew I needed a number of fields from the player’s table, unit’s table and item’s table. (Items are things that the unit holds)
I knew I wanted to display all players that had registered, so I started there.
Select user.username, user.numKills
from user
where user.isRegistered = ‘Y’
I then wanted to display the unit’s name, class and life
Select user.username
, user.numKills
, unit.unitName
, unit.class
, unit.life
from user
left outer join unit on unit.ownerID = user.userID
where user.isRegistered = ‘Y’
I then wanted to display all the items that the unit held, and the item’s description
Select user.username
, user.numKills
, unit.unitName
, unit.class
, unit.life
, item.itemName
, item.itemDescription
from user
left outer join unit on unit.ownerID = user.userID
left outer join item on item.unitID = unit.unitID
where user.isRegistered = ‘Y’
This was the sql that I ended with.
Further Articles
Good ideas to follow this up with:
- Updates, Inserts and Deletes
- Restricting an inner or outer join
- Links and Resources
mobeamer
www.BattleForcesOnline.com
blogspot.mobeamer.com
I am no author but I do have somethings to share.
Anyways, the result is out now, and you can check out who won what!
