0
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?

closed as off-topic by ypercubeᵀᴹ, Paul White, Mark Storey-Smith, Max Vernon, RolandoMySQLDBA Sep 23 '14 at 22:00

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – ypercubeᵀᴹ, Paul White, Mark Storey-Smith, Max Vernon, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • sqlfiddle.com/#!2/3accb/1 – Mihai Aug 23 '14 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 '14 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 '14 at 15:19
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 |
+----+-------+
  • it worked for me, thx – Naix Aug 23 '14 at 12:17
  • 1
    @Naix, This query is exactly what you have in the question! How did "this worked" and the original failed?! – ypercubeᵀᴹ Sep 20 '14 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.

  • 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 '14 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 '14 at 12:52
  • sorry, you are right. "left join" means "left outer join". "left inner join" does not make nay sense. – miracle173 Aug 23 '14 at 13:11

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