Differentiating between 'Joins' and Where clauses in SQL queries

Simply speaking, a select statement is used to select data from tables depending on a particular criteria. Simple. Isn't it? Now how best to do it? I follow the following simple steps:

1. Determine what fields you want and from which table each of these fields are coming from.
2. Determine the join criteria with which you would join these tables to get the common data.
3. Detemine whether you need to do an inner join or an outer join.
4. Determine what goes into the "where" clause to purify the retrieved rows.
5. If there are more than 4 tables to be joined, Sybase is usually clueless about the order of joins.

So determine the order of joins.

Most of the developers have a habit of mixing up the where clause with the join. This makes a query complex and hard to debug.When you differentiate, you specify
1. What goes into a join, .i.e the criteria of commonility between two tables based on which the
combined rows are retrieved &
2. What goes into a 'where' clause .i.e Criteria on which the retrieved rows are filtered.

This make the query clean, more readable and inturn faster to debug. Let me eloborate further. When there is a join in a query, the ASE gets ready to retrieve the cartesian product of the tables. For example, if there are two tables EmpAddress(5 Cols, 10 Rows) & EmpName(4 Cols 8Rows). When you say join, the ASE tries to combine each of the 50 elements in the table EmpAddress with each of the 32 elements in table EmpName using all permutations and combinations. When you specify a join, the ASE selects only those elements with a commonality. For example both tables have a common column EmpID, when you write a join statement like

SELECT
EmpAddress.*,
EmpName.*
FROM EmpAddress A
INNER JOIN EmpName N ON N.EmpID = A.EmpID

You are specifying that the ASE should bring only those rows which have the same EmpID in both the tables. eg, if EmpID= 233,234,235 are common in both the tables, it will get the Address row from EmpAddress(A) and the Name row from EmpName(B) for EmpIds 233,234,235. All the other combinations are ignored.

About the where clause, if you want to select only the details of employee with EmpId 233, then you specify,

SELECT

EmpAddress.*,

EmpName.*
FROM

EmpAddress A INNER JOIN EmpName N ON N.EmpID = A.EmpID

Where

A.EmpId = 233

Compare this with

SELECT

EmpAddress.*,

EmpName.*

FROM

EmpAddress A,

EmpName N

WHERE

A.EmpID=N.EmpID AND

A.EmpId=233.

Since the query here is simple, you might not get the entire picture. Imagine having to join 5 tables, with 10 filter criteria?The query gets patch, muddled and complex.

One quick point about differnce between 'where' clause and 'Having' clause. Having is used to specify filter criteria on aggregate functions.Say suppose you write a query to calulate the average salary of all the empolyees and filter those with <50000.>

SELECT

AVG(Sal)

FROM

EmpSal S

INNER JOIN EmpDetails D on D.EmpID = S.EmpID

WHERE

S.EmpId in (233, 234,235)

HAVING

AVG(Sal) > 50000