Alright, so last time I closed out my(sql) post I promised some more advanced queries etc. Well let’s first start by playing around with some basic query syntax.
Think back to what I wrote about it being a logical grouping of data like a spreadsheet. So surely we can pull specific data right? Of course, this is where sql shines, and it couldn’t be simpler.
select user_id0 from temp.test_users;
Pretty simple right? Ok, well let’s grab user id’s and their location, that should also be simple enough.
select user_id0, location from temp.test_users;
Now how about just the user’s located in Birmingham, al?
select user_id0, location from temp.test_users where location like 'birmingham%';
Ok, let’s take a moment now and examine what we’ve just covered here in such rapid succession. These are simple select statements, and the primary data retrieval method in SQL.
select <column, names> from <server.database.table> [where <conditions of the search>]
You can use * for column names to select them all, and using where is not required for your statements, but advised for a precise query. Couldn’t be easier could it? A cool little tidbit too is using as in your query to modify your column names, using our previous select for user id’s and location lets do it again and rename our columns User ID and Place:
select user_id0 as 'User ID', location as 'Place' from temp.test_users where location like 'birmingham%';
What? That’s it? Pretty nifty huh? Ok, let’s turn the difficulty up a little bit now and join tables for a cross reference. Ok, I built a bogus phonebook table with similar data to our test_users table. In that table I have some phone numbers, but not the full name of the users. I want to pull the users full name and phone number, here’s how I could do that:
select test.last_name, test.first_name, phone.number from test_users as test left outer join phonebook as phone on phone.userid = test.user_id0;
Ok, you can put the paper sack away, and breathe easy. That’s not nearly as complex as it looks or sounds. Let’s break it down piece by piece and examine it real fast. First we will look at the output:
+-----------+------------+--------------+ | last_name | first_name | number | +-----------+------------+--------------+ | belcher | daniel | 205-111-1111 | | rich | derrek | 813-111-1113 | | george | amanda | 205-111-1112 | +-----------+------------+--------------+
In our select statement we see column last_name, first_name, and number. We examined earlier about using ‘as’ to define a name, and that’s what we have done during that select statement. We’ve named the table test_users as test and phonebook table as phone.
So, before defining the column name we are selecting, we have to identify FROM which table. In this case; test.last_name, test.first_name, phone.number
Now, let’s examine the data I put into phonebook to better understand how this join is working.
+--------+---------+----------------+--------------+ | userid | last | location | number | +--------+---------+----------------+--------------+ | dbel | belcher | birmingham, al | 205-111-1111 | | ageo | george | birmingham, al | 205-111-1112 | | drich | rich | tampa, fl | 813-111-1113 | +--------+---------+----------------+--------------+
Ok, as you can see, the userid column is identical to our user_id0 from test_users which makes it a prime anchor point for our join. So we perform a left outer join on test.user_id0 and phone.userid.
Now wasn’t that easy?
Finally we are going to look at a subquery.
select user_id0, last_name, first_name
from test_users
where location in ('birmingham, al')
order by user_id0;
This should prove very simple to read. If the data stored under location matches birmingham, al then it is pulled. You could easily add more criteria to the subquery by adding it along with a comma to separate the value. So; where location in (‘birmingham, al’,’tampa, fl’)
There are other ways to achieve similar outcomes using OR and AND, but this is a much cleaner and shorter method for doing it.
That’s it for now, perhaps I’ll have another sql post with even more interesting sql techniques in the future.