sql query to join 2 tables and show all records from 1 column

Question

i have two tables in sql server - one with 51 american states, and the other with name,state. the table with name state has different records namely -

Seere -- AK
Seere -- LA
Seere -- CA
John  -- HI
John  -- MA

I want a query that picks up one name say "Seere" and shows all the states from state table, and the name attached to those states which are from second table, so

null -- AR
Seere -- AK
Seere -- LA
Seere -- CA
null -- MA
null -- CO

same for all the names, I just pick one name and all states show. any ideas?

Answer 1

Use an outer join:

select name_state.name, state_table.state
  from state_table
    left outer join name_state
      on (state_table.state = name_state.state)
  where name_state.name = "Seere"

Answer 2

SELECT  *
FROM    states s
LEFT JOIN
        names n
ON      n.name = 'Seere'
        AND n.state = s.state