Monday, January 10, 2011

Inner joins in TSQL

Source: TSQL Programming Reference:

An inner join applies two logical query processing phases—it applies a Cartesian product between the two input tables like a cross join, and then it filters rows based on a predicate that you specify. Like cross joins, inner joins have two standard syntaxes: ANSI SQL-92 and ANSI SQL-89.

Using the ANSI SQL-92 syntax, you specify the INNER JOIN keywords between the table names. The INNER keyword is optional because an inner join is the default, so you can specify the JOIN keyword alone. You specify the predicate that is used to filter rows in a designated clause called ON. This predicate is also known as the join condition.

Here are two basic implementations of inner join scripts i created, one with the inner keyword, and the other w/o the inner keyword.


1:  select distinct l.LoanNum, (c.CusLastName + ', ' + c.CusFirstName + ' '  
2:     + c.CusMiddleInit) as Name, Status, DateGranted, AmountGranted  
3:            from loanmaster l  
4:            join customer c on c.CustomerID = l.CustomerID  
5:            where l.loannum = 4  
6:  select distinct l.LoanNum, (c.CusLastName + ', ' + c.CusFirstName + ' '  
7:     + c.CusMiddleInit) as Name, Status, DateGranted, AmountGranted  
8:            from loanmaster l  
9:            inner join customer c on c.CustomerID = l.CustomerID  
10:            where l.loannum = 4  

0 comments:

Post a Comment