SQL Basics - Using Joins

What are joins? We explain in last post (Subqueries) what relational databases are. Basically, there are many different groups of data stored in many different tables. We used subqueries to filter data based on multiple tables. Now what if we need to retrieve data from multiple tables. Well, we can do many select squeries with many subqueries and add them all together. But there must be an easier way. That is what we use joins for. What joins do is virtually connect all the tables based on the columns that connect them, so that for our application we treat it as one big table.

Creating a join is very similar to what we've been doing up until now. Two things change. In the FROM clause, we put in all the table names that we're using. Second, we must match the columns with a WHERE statement. For example:

SELECT OrderItems.order_num,OrderItems.prod_id,Vendors.vend_name
FROM OrderItems,Vendors,Products
Where OrderItems.prod_id=Products.prod_id AND Products.vend_id=Vendors.vend_id;

What we did is use a WHERE clause to join the columns by showing which columns connect them. Then in the FROM clause we included all the tables we're working with.

You'll realize something else new here. I have the columns fully qualified, meaning tablename.colname . Being that we're using multiple tables and they share column names, we must tell the SQL application which column we mean, or you'll get an error message. Take a look at the example we gave in subqueries. Can you see how much simpler it would be using joins.

To further simplify our job, we can use aliases (which we discussed in a previous post). Using the above example, here's how we would do it.

SELECT O.order_num,O.prod_id,V.vend_name
FROM OrderItems AS O, Vendors AS V,Products AS P
Where O.prod_id=P.prod_id AND P.vend_id=V.vend_id;

We named each column by their initial, and that made life a lot easier, or at least this query easier. There is more to be written about joins, but this will suffice for us. The other types are much more advanced and won't be used in your regular playing around, especially in SQL injecting. Did you forget that that's out real goal here?