How to join two tables in sql server 2008?

same like
which join you want?

SELECT order_id, product_name, unit_price, supplier_name, total_units
FROM product, order_items
WHERE order_items.product_id = product.product_id;

or another Syntax

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

have a look at the following:
http://www.dotnetspider.com/resources/635-SQL-Joins-wi-Examples.aspx[^]

http://www.w3schools.com/sql/sql_join_left.asp[^]

http://www.gplivna.eu/papers/sql_join_types.htm[^]

SELECT FLD1, FLD2 FROM TABLE1 INNER JOIN TABLE2 
ON TABLE1.PRIMARY_KEY=TABLE2.FOREIGN_KEY

OR

SELECT FLD1, FLD2 FROM TABLE1 , TABLE2 
WHERE TABLE1.PRIMARY_KEY=TABLE2.FOREIGN_KEY

Above we are matching the fields of both the tables and extract the record only when the record exists in both the tables.

In case of Inner join above both are same and you can use as you like.

SELECT productID,productName,categoryName
FROM products
INNER JOIN categories ON products.categoryID = categories.categoryID

here Product and categories are tables
go thru this
http://www.sqltutorial.org/sqljoin-innerjoin.aspx[^]