1
CREATE TABLE `table1` (
`id` INT(10) NOT NULL 
)
ENGINE=InnoDB;

CREATE TABLE `table2` (
`id` INT(10) NOT NULL,
`value` INT(10) NOT NULL
)
ENGINE=InnoDB;

INSERT INTO table1
VALUES (1), (2), (3), (4);

INSERT INTO table2
VALUES (1, 100), (2, 200);

I want to get table like:

id value
1  100
2  200
3  null
4  null

My query is

SELECT t1.id, t2.value
FROM table1 t1
LEFT JOIN table2 t2
    ON t2.id = t1.id

and I got:

1 100
2 200

How should I query the database to get what I want?

3
  • sqlfiddle.com/#!2/3accb/1
    – Mihai
    Aug 23, 2014 at 12:59
  • "left join" means "left outer join". So the query does exactly the same as the accepted answer. There is something wrong with your question. can you check your question again?
    – miracle173
    Aug 23, 2014 at 13:38
  • +1 for providing DDL and DML - if only all posters did the same. Re. the default being an OUTER JOIN anyway, to get the result you showed in the question you must have used something like SELECT t1.id, t2.value FROM table1 t1 INNER JOIN table2 t2 ON t2.id = t1.id;
    – Vérace
    Aug 23, 2014 at 15:19

2 Answers 2

0

Try this

SELECT t1.id, t2.value
FROM table1 t1
LEFT OUTER JOIN table2 t2
    ON t2.id = t1.id;

+----+-------+
| id | value |
+----+-------+
|  1 |   100 |
|  2 |   200 |
|  3 |  NULL |
|  4 |  NULL |
+----+-------+
2
  • it worked for me, thx
    – Naix
    Aug 23, 2014 at 12:17
  • 1
    @Naix, This query is exactly what you have in the question! How did "this worked" and the original failed?! Sep 20, 2014 at 7:40
1

hye i tried below query in mysql and SQL server databases its working fine, so please check once again. and tell me which database u tried.

SELECT t1.id, t2.value FROM table1 t1 LEFT JOIN table2 t2 ON t2.id = t1.id

id   value  
--  --------
 1       100
 2       200
 3    (NULL)
 4    (NULL)

Thanks.

3
  • no, it cannot work because you use an inner join. Maybe you entered too much data into table2. table2 should contain only two rows.
    – miracle173
    Aug 23, 2014 at 12:38
  • no, i did not entered to much data only two columns i inserted as u gave. that is not Inner join , it is left join , so i got correct answer.
    – Pydi Raju
    Aug 23, 2014 at 12:52
  • sorry, you are right. "left join" means "left outer join". "left inner join" does not make nay sense.
    – miracle173
    Aug 23, 2014 at 13:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.