create table tie 
( 
    id int, 
    price int 
) 
insert into tie values (1, 20); 
insert into tie values (1, 30); 
insert into tie values (1, 40); 
insert into tie values (1, 50); 
insert into tie values (1, 60); 
insert into tie values (1, 60);

select top 5 with ties from tie 
order by price asc; 

Results
1 20
2 30
3 40
4 50
5 60
6 60
The first 6 rows are part of the result set. 6 rows because row number 5 and 6 have the same price value. The price value is looked at because the WITH TIES clause has an ORDER BY on price. An order by clause is mandatory with the WITH TIES construct.

Start situation (download the sql script here)

Table A Table B
1 2
2 3
3 4

The INNER join (“normal / regular” join)

select a.id as A, b.id as B from a inner join b on a.id = b.id; 

Results
A B
2 2
3 3

Only rows that have the same id in both tables appear in the result set.

The LEFT OUTER join

select a.id as A, b.id as B from a left join b on a.id = b.id; 

Results
A B
1 NULL
2 2
3 3

Every row from the table mentioned on the left (table A in this example) will be part of the result set. If possible the corresponding row (the one with the same id) in table B is also part of the result set. If no corresponding row is found in table B NULL values are provided.
The RIGHT OUTER join

select a.id as A, b.id as B from a right join b on a.id = b.id; 

Results
A B
2 2
3 3
NULL 4

Every row from the table mentioned on the right (table B in this example) will be part of the result set. If possible the corresponding row (the one with the same id) in table A is also part of the result set. If no corresponding row is found in table A NULL values are provided.
The FULL OUTER join

select a.id as A, b.id as B from a full join b on a.id = b.id; 

Results
A B
1 NULL
2 2
3 3
NULL 4

Every row from table A and table B is part of the result set. If possible a match is made based on the id. If a match could not be made NULL values are provided for the missing items.
Cartesian product
select a.id as A, b.id as B from a , b;
Results
A B
1 2
2 2
3 2
1 3
2 3
3 3
1 4
2 4
3 4

Every row in table A is joined with every row in table B. So the cartesian product of table A and table B delivers 9 rows in the result set. A cartesian produkt is often specified by mistake (missing join condition).