SIMPLE JOINS

To demonstrate the Joins between two tables in SQL, we are going to take the following two tables called STATE and CITY. In STATE table STATE_CD is the primary key and in CITY table STATE_CD and CITY_NAME makes the primary key.

STATE table

CITY table

We can join two tables if there is a common column exists in both. In the above example we have a common column called STATE_CD, so we can percorm join on these tables.

Say you want to display the CITY_NM, POPULATION, STATE_DESC from the above two tables.

Here we go, CITY_NM and POPULATION columns exists in CITY table, STATE_DESC column is in STATE table. We are going to show information from two different tables, so in the FROM clause of SELECT statement we should include two tables.

SELECT CITY_NM, POPULATION, STATE_DESC

FROM CITY, STATE

WHERE CITY.STATE_CD = STATE.STATE_CD;

The result for the above query

How this JOIN works. Each database engine chooses the different ways to process the result set. To understand the JOIN concept better we will take the above example. In the FROM clause we have CITY, STATE tables, so it merges the rows from two tables and makes it as one temporary table in the server memory area, so it will be like

Now the WHERE clause applies on the above shown temporary table. According to the WHERE clause in our select statement state_cd in city table should be equal to state_cd in state table ie (CITY.STATE_CD = STATE.STATE_CD). To show the result set we have to select those records in which state_cd is equal. So in the following table all the records we marked in black is selected and those one marked in reds are discarded as the state_cd is different. This is the way it will filter the un wanted records when you do a join.

So after filtered the un wanted records the work table looks like

What are the columns we have in our SELECT statement those are the only columns that will be in our result set, all other columns will get discarded at this final time.

That is how the result set will get processed inside the database engine.