Query with optional parameter?

[godsent]

Lets say I have two tables:

 

Table1

 

Id Name

1 Joe

2 Greg

3 Susan

4 Max

 

 

Table2

 

Uid comment

2    Good customer

4    Great guy

 

 

What I want to do is list all elements of Table 1, and if Table1.Id = Table2.Uid I want to select this comment. If comment does not exist give blank field.

 

Result should be:

1 Joe 

2 Greg Good customer

3 Susan

4 Max Great Guy

 

 

I can't figure out how to do it, if I write:

select
table1.Id,
table1.Name,
table2.comment

where
table1.id=table2.Uid

It gives me only users 2 and 4.

 

Do you have any ideas?

[requinix]

Use a proper JOIN in your query, not the form where you specify multiple tables in the FROM.

SELECT
table1.Id,
table1.Name,
table2.comment
FROM table1
JOIN table2 ON table1.Id = table2.Uid

That will still give you 2 and 4. Now turn it into a LEFT JOIN

...FROM table1
LEFT OUTER JOIN table2 ON table1.Id = table2.Uid...

The LEFT means that for every row in the "left" table (table1) there may not be corresponding rows in the "right" table (table2). With the default INNER JOIN, if there are no matching rows then the row from the left table is thrown out; with an OUTER JOIN (be it LEFT or RIGHT) the row is kept and the missing values become all NULL.

Id | Name  | comment
---+-------+--------
 1 | Joe   | NULL
 2 | Greg  | Good customer
 3 | Susan | NULL
 4 | Max   | Great Guy

[godsent]

great answer, thank you!