question

I wouldn't really call a cross join as 'normal behaviour' as that gives you a Cartesian product : every row in one table joined to every other in another. I would class an inner join as 'normal' - this is the most common join I use, especially when following FK constraints. Joins are often best explained using visuals - Venn diagrams are great for this : http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ but notice how Venn diagrams can't really display a cross join. There's not one join function as each of the different join types results in a different result set - can't see how else you could acheive this. As for the 2nd question, do you mean why can you not use an alias in the WHERE clause? The reason is due to the order in which the query is executed - aliases are given in the select clause and this is the ***last*** part to be executed - so the WHERE clause has no concept of the alias.

Here is a good poster by Itzik Ben-Gan -- http://tsql.solidq.com/books/insidetsql2008/Logical%20Query%20Processing%20Poster.pdf -- it should answer your questions about the joins and why you cannot name a column in the WHERE clause to then use in the SELECT clause. It's all about the logical query processing! Let me know if it's still not clear to you. On this poster is exactly what I am saying. That there is just one join. On poster there is only one join and that is cartesian product. Then ON and then add outer rows. So engine behind joins is just one function for join. But in SQL language there is cross, inner, left, right for no reason. I guess they did it because it can be easier to learn and use for some people but definitly not for me.

There are different JOIN patterns because data is stored in different ways. I'd modify your approach slightly to say that the INNER join is the normal behavior. We're talking relational data storage, so one table relates to another. That relationship, defined the foreign key, is best represented when talking about an INNER join, not a CROSS join (which is what is known as a Cartesian product, everything joining on everything, which is not defined by a relationship). OUTER joins, left or right (and that's just a construct to support placement of the code), are basically situations where sometimes there's data in a relationship and sometimes there is not. So the OUTER join allows for all the data from one table and only the data that matches from the other. As for defining column names in the WHERE clause, that's for filtering. Assuming you meant the FROM clause, you actually can. It's called a derived table. There are several ways to make these in SQL Server. Here's a pseudo-code example: SELECT a.Brown FROM (SELECT b.Green AS Brown FROM dbo.TableB AS b) AS a By putting that SELECT in the FROM criteria, I could put aliases in, and then use those aliases in the outer query.