Tag Archives: join statement

More SQL JOIN Statements

Posted on by
Reply

Lets look at another way of querying with JOIN statement. Just to recap from previous post that JOIN statement aka INNER JOIN return rows when there is at least one match in BOTH tables

select e.emp_name as "Employee Name" , d.dept_name "Department Name"
from employee e , department d
where e.emp_dept_id = d.dept_id and d.dept_budget > 500000;

So what is the difference compared to what we have looked at last time? The difference is slight , the main logic stays the same but how we name the names have changed. take a look at the second line

from employee e , department d

Here we are saying “employee” table from now on will be referenced by “e” for the lifetime of the SQL statement. If you come from traditional programming background , like me , you might find it odd that “e” can be used on the first line yet it is only defined in the second line. You definitely cannot use a variable that has not been defined!

Anyway , besides that weirdness , every other part remains the same. Of course they are now “e.emp_name” instead of “employee.emp_name”. This query will return ,

Employee Name Department Name
Michael Bay Asia
John Evan Finance
Christine May Sales
George Chan Asia

SQL JOIN statement tutorial with examples

Posted on by
Reply

Today , lets go into the exciting world of JOIN statements. Before we talk about complex topics such as INNER and OUTER JOINS , default JOIN is an INNER JOIN statement if you are interested , a few words on the concept of JOIN statements. We have , up to this stage , done some basic SQL operations , such as setting up the database and the tables and inserting information into them. We have also looked at basic queries using SELECT statement with WHERE clause. Till now , we are learning what can be done even easier in excel , not much more reason to learn SQL at all. What , then , is the big deal with the databases?

Short and sweet answer to that would be that a database , is a relational database. What this means is that by using common information , we can gather information from two tables at same time. Take a look at the table created , each table is specific to itself. There is no department budget data in employee table and there is no reason why it should be there. If we want to know what is our own department budget , we ask our manager , who has access to it. Same for the SQL database. Instead of adding every information to one table only , we split the tables according to the function or type , pretty much like OOP if you ask me.

Here is a sample JOIN statement , 

select employee.emp_name , department.dept_name , department.dept_budget
  from employee , department
  where department.dept_budget >500000;

Here , instead of querying from one table , we query from 2 tables , employee and department tables. If you read it carefully 2 or 3 times , you will find it is pretty simple and straightforward. In plain English , it is asking for

“Employee names , department names and department budget for those departments with budget more than 500000.”

There are a few ways of using JOIN statements but for our purpose , this will do. Till next time , have a nice day!