Inner Join problem

Answer this question

Inner Join problem

DongL
If you don't have BOL, you can download it from here.
http://www.microsoft.com/sql/downloads/2000/default.mspx

And also some other SQL Server related books can be found here.
http://www.microsoft.com/sql/techinfo/books.mspx

/Kenneth

gshir

SELECT *
FROM items
JOIN products ON items.product_id = products.product_id
AND release_date > CURRENT_DATE;

In the above , would you please explain its algorithm logic
How does it process

hi mert-1

are items different from your product or maybe the items are the orders, anyways I'll take the 2nd assumption.. it will display all products which will be released after the current date or future releases.

SELECT DISTINCT product
FROM products, items
WHERE products.product_id = items.product_id
AND items.release_date > CURRENT_DATE;

How can product table components be compared with items table components
What is the process logic of this
For instance, maybe it first look to the first record of products table
and then compare it to the all record of the items table, is it true

the database will try to match all items against the products table using the product_id column

The same situation is valid for the below snippet, is not it
First, SQL server takes the first record(row) of the employees table and then SQL server compare this record to each invoices table's records. So , if it finds any corresponding row,
it prints to the monitor, is not it

SELECT employees.Lastname, employees.Firstname, invoices.Sale, invoices.Price
FROM employees
INNER JOIN invoices
ON employees.id = invoices.EmployeeID

it will display the invoice cost of employees who're invoiced.

HTH

alt

SELECT *
FROM items
JOIN products ON items.product_id = products.product_id
AND release_date > CURRENT_DATE;

In the above , would you please explain its algorithm logic
How does it process

Another way to look at this is the clause order. Each executes in an order that is very important to understanding how the query works. This is the order (I can't remember where a good resource on this subject is, but I will find one) that things logically happen, and if you are trying to figure out why something isn't working, this method is mathematically equivalent to whatever path is fastest that the optimizer chooses. Obviously not all clauses need to be present, so these clauses are skipped.

FROM - Each table is evaluated from the first to the last (they can be rearranged by the optimizer to make the join faster, but logically they pretty much occur from left to right, though you can use parenthesis to change this order.) The output is every matching row from the joined tables.

WHERE - Each row from the FROM set is checked against the criteria in this clause

GROUP BY - The rows from the WHERE are grouped together based on the criteria here. So now you have a sort of one to many set where any value represented in the GROUP BY clause is the one, and every other value is repeated in a vector of values (these repeating values can only be used in aggregate functions in the following clauses.)

HAVING - Filters groups from the GROUP BY clause. You can use aggregates in the HAVING clause, but not in the WHERE clause

ORDER BY - the next to the last step is to sort the values for output.

SELECT - Takes the set as it appears now and applies the scalar formulas to each column and places on a set for output. Any vector values must be used in aggregates, like SUM, MAX, etc. This is the value that will be added to the output values that will be eventually be displayed on the screen.

Hope this helps a bit. Whenever I find a more complete resource I will post it for you.

greesman_jr

Thanks for your replies,
Do you have really beneficial SQL tutorial references with many details
I could not find a really good tutorial except one.
Orchid2006

Merit,
The best way to understand joins is read BOL. There are several algorithms to perform a join, such as inner loop join, merge join and hash join. Each of them needs thorough coverage that is impossible to provide in one post. I will give 2-word description of each type of joins, but, yet again, you need to read BOL to understand everything in detail.
Inner loop join works like you guessed-it devides the tables to be joined to inner and outer tables. Then, it iterates through outer table, and for each row in outer table performs index seek/scan/table scan in the inner table to find matching rows.
Merge join sorts the tables by the column that is used for join. Then, the two sorted temporary tables are merged. Merge join is particularly effective when the tables are sorted already(i.e. there is a clustered index on them on the column by which it has to be sorted).
Hash join is performed by creating a hashtable from one of the tables(this table is named build input). Then, the other table(called probe input) is scanned, and for each row in probe table the matching rows are looked up in the hashtable. There are some modifications of hash join-refer to BOL for details.
Note that these are algorithms for performing a join. Whatever algorithm the server chooses, the result is the same. The algorithm only affects performance issues-sometimes one of the algorithms performs considerably better than the others.

Hope this helps.
praveengoosari
BOL is the best. If you are absolutely new to SQL, by a book and use BOL as you read through the book. Tutorials definitely will not help much.
Inner Join problem

Answer this question