Compare two MySQL tables and select distinct entries in second one
up vote
0
down vote
favorite
I have two MySQL tables with one column in each. I want to select the entries in second table that does not exist in the first one.
I tried the below query, but seems does not work
SELECT column_name FROM `table_name_1`
WHERE NOT EXISTS (SELECT column_name FROM `table_name_2`);
For example:
table_name_1
column
111111111111
222222222222
333333333333
444444444444
table_name_2
column
222222222222
333333333333
555555555555
666666666666
Now, I want to get only 55555555555 and 66666666666 entries in table_name_2.
mysql mysql-workbench mysql-error-1064
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
asked Nov 1 at 10:59
Ahmed Maher
227
add a comment |
up vote
0
down vote
favorite
I have two MySQL tables with one column in each. I want to select the entries in second table that does not exist in the first one.
I tried the below query, but seems does not work
SELECT column_name FROM `table_name_1`
WHERE NOT EXISTS (SELECT column_name FROM `table_name_2`);
For example:
table_name_1
column
111111111111
222222222222
333333333333
444444444444
table_name_2
column
222222222222
333333333333
555555555555
666666666666
Now, I want to get only 55555555555 and 66666666666 entries in table_name_2.
mysql mysql-workbench mysql-error-1064
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
asked Nov 1 at 10:59
Ahmed Maher
227
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 1 at 11:02
SELECT DISTINCT column_name FROMtable_name_2WHERE NOT EXISTS (SELECT column_name FROMtable_name_1);
– mbharanidharan88
Nov 1 at 11:26
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have two MySQL tables with one column in each. I want to select the entries in second table that does not exist in the first one.
I tried the below query, but seems does not work
SELECT column_name FROM `table_name_1`
WHERE NOT EXISTS (SELECT column_name FROM `table_name_2`);
For example:
table_name_1
column
111111111111
222222222222
333333333333
444444444444
table_name_2
column
222222222222
333333333333
555555555555
666666666666
Now, I want to get only 55555555555 and 66666666666 entries in table_name_2.
mysql mysql-workbench mysql-error-1064
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
asked Nov 1 at 10:59
Ahmed Maher
227
I have two MySQL tables with one column in each. I want to select the entries in second table that does not exist in the first one.
I tried the below query, but seems does not work
SELECT column_name FROM `table_name_1`
WHERE NOT EXISTS (SELECT column_name FROM `table_name_2`);
For example:
table_name_1
column
111111111111
222222222222
333333333333
444444444444
table_name_2
column
222222222222
333333333333
555555555555
666666666666
Now, I want to get only 55555555555 and 66666666666 entries in table_name_2.
mysql mysql-workbench mysql-error-1064
mysql mysql-workbench mysql-error-1064
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
asked Nov 1 at 10:59
Ahmed Maher
227
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
asked Nov 1 at 10:59
Ahmed Maher
227
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
edited Nov 1 at 11:24
Madhur Bhaiya
19.3k62236
19.3k62236
asked Nov 1 at 10:59
Ahmed Maher
227
asked Nov 1 at 10:59
Ahmed Maher
227
asked Nov 1 at 10:59
Ahmed Maher
227
227
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 1 at 11:02
SELECT DISTINCT column_name FROMtable_name_2WHERE NOT EXISTS (SELECT column_name FROMtable_name_1);
– mbharanidharan88
Nov 1 at 11:26
add a comment |
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 1 at 11:02
SELECT DISTINCT column_name FROMtable_name_2WHERE NOT EXISTS (SELECT column_name FROMtable_name_1);
– mbharanidharan88
Nov 1 at 11:26
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 1 at 11:02
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 1 at 11:02
SELECT DISTINCT column_name FROM
table_name_2 WHERE NOT EXISTS (SELECT column_name FROM table_name_1);– mbharanidharan88
Nov 1 at 11:26
SELECT DISTINCT column_name FROM
table_name_2 WHERE NOT EXISTS (SELECT column_name FROM table_name_1);– mbharanidharan88
Nov 1 at 11:26
add a comment |
3 Answers
3
active
oldest
votes
up vote
2
down vote
You can simply Left Join from table 2 to table 1 and get all those unique values, corresponding to whom no match exists in the table 1 (t1.column_name is null)
Also, note that, for performance you will need Index on both the columns in their respective tables.
Try the following instead:
SELECT DISTINCT t2.column_name
FROM table_name_2 AS t2
LEFT JOIN table_name_1 AS t1 ON t1.column_name = t2.column_name
WHERE t1.column_name IS NULL
Note that if the column values are constrained to be unique (PK or Unique constraint), or you don't care if duplicate values come in result, you can remove DISTINCT keyword used in the above query.
Result:
| column_name |
| ------------ |
| 555555555555 |
| 666666666666 |
View on DB Fiddle
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:58
@AhmedMaher do you have any indexing defined on the tables ?
– Madhur Bhaiya
Nov 20 at 12:01
No, both tables are 1 column tables and that column is the primary one.
– Ahmed Maher
Nov 21 at 8:28
@AhmedMaher since the column is Primary Key. You can remove theDISTINCTfrom my query and try again.
– Madhur Bhaiya
Nov 21 at 8:52
add a comment |
up vote
0
down vote
Your query should work with this. Check fiddle
SELECT DISTINCT column_name
FROM table_name_2
WHERE column_name NOT IN (SELECT column_name
FROM table_name_1);
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
Also as the other solution, both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
add a comment |
up vote
0
down vote
So some records from the second table are needed.
Then the second table must be in the outer query.
The (NOT) EXISTS criteria is different from (NOT) IN.
When using an EXISTS, the relation between the query in the EXISTS and the outer query needs to be added inside the EXISTS.
SELECT DISTINCT column_name
FROM `table_name_2` AS t2
WHERE NOT EXISTS (
SELECT 1
FROM `table_name_1` AS t1
WHERE t1.column_name = t2.column_name
);
SqlFiddle test here
answered Nov 1 at 11:33
LukStorms
11.2k31532
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Then I assume that there's no index ontable_name_1.column_name. I wouldn't know about why there's no result from your real tables. Maybe t1 has a matching column_name in t2 for all?
– LukStorms
Nov 20 at 12:08
@AhmedMaher One other trick you could try is using a temporary table with a primary key on column_name. Then insert the unique values to it. F.e.insert into tmpTable (column_name) select column_name from table_name_1 where column_name is not null group by column_name. Then change the query so it uses the temporary table instead. It could be faster because it could have less records, and it would be indexed on the field you try to match.
– LukStorms
Nov 20 at 13:25
There are results, I know that. Both tables are 1 column tables and that column is the primary one.What about altering both table and adding a separate primary key column? Would that help?
– Ahmed Maher
Nov 21 at 8:29
@AhmedMaher I kinda assumed that your real tables would have more columns and that you simplified the issue for your question. Then why not just make that one column the primary key in both tables? You can't insert dups in it afterwards though. Also that would open up another possible solution : the MINUS operator.
– LukStorms
Nov 21 at 8:45
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53099904%2fcompare-two-mysql-tables-and-select-distinct-entries-in-second-one%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
You can simply Left Join from table 2 to table 1 and get all those unique values, corresponding to whom no match exists in the table 1 (t1.column_name is null)
Also, note that, for performance you will need Index on both the columns in their respective tables.
Try the following instead:
SELECT DISTINCT t2.column_name
FROM table_name_2 AS t2
LEFT JOIN table_name_1 AS t1 ON t1.column_name = t2.column_name
WHERE t1.column_name IS NULL
Note that if the column values are constrained to be unique (PK or Unique constraint), or you don't care if duplicate values come in result, you can remove DISTINCT keyword used in the above query.
Result:
| column_name |
| ------------ |
| 555555555555 |
| 666666666666 |
View on DB Fiddle
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:58
@AhmedMaher do you have any indexing defined on the tables ?
– Madhur Bhaiya
Nov 20 at 12:01
No, both tables are 1 column tables and that column is the primary one.
– Ahmed Maher
Nov 21 at 8:28
@AhmedMaher since the column is Primary Key. You can remove theDISTINCTfrom my query and try again.
– Madhur Bhaiya
Nov 21 at 8:52
add a comment |
up vote
2
down vote
You can simply Left Join from table 2 to table 1 and get all those unique values, corresponding to whom no match exists in the table 1 (t1.column_name is null)
Also, note that, for performance you will need Index on both the columns in their respective tables.
Try the following instead:
SELECT DISTINCT t2.column_name
FROM table_name_2 AS t2
LEFT JOIN table_name_1 AS t1 ON t1.column_name = t2.column_name
WHERE t1.column_name IS NULL
Note that if the column values are constrained to be unique (PK or Unique constraint), or you don't care if duplicate values come in result, you can remove DISTINCT keyword used in the above query.
Result:
| column_name |
| ------------ |
| 555555555555 |
| 666666666666 |
View on DB Fiddle
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:58
@AhmedMaher do you have any indexing defined on the tables ?
– Madhur Bhaiya
Nov 20 at 12:01
No, both tables are 1 column tables and that column is the primary one.
– Ahmed Maher
Nov 21 at 8:28
@AhmedMaher since the column is Primary Key. You can remove theDISTINCTfrom my query and try again.
– Madhur Bhaiya
Nov 21 at 8:52
add a comment |
up vote
2
down vote
up vote
2
down vote
You can simply Left Join from table 2 to table 1 and get all those unique values, corresponding to whom no match exists in the table 1 (t1.column_name is null)
Also, note that, for performance you will need Index on both the columns in their respective tables.
Try the following instead:
SELECT DISTINCT t2.column_name
FROM table_name_2 AS t2
LEFT JOIN table_name_1 AS t1 ON t1.column_name = t2.column_name
WHERE t1.column_name IS NULL
Note that if the column values are constrained to be unique (PK or Unique constraint), or you don't care if duplicate values come in result, you can remove DISTINCT keyword used in the above query.
Result:
| column_name |
| ------------ |
| 555555555555 |
| 666666666666 |
View on DB Fiddle
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
You can simply Left Join from table 2 to table 1 and get all those unique values, corresponding to whom no match exists in the table 1 (t1.column_name is null)
Also, note that, for performance you will need Index on both the columns in their respective tables.
Try the following instead:
SELECT DISTINCT t2.column_name
FROM table_name_2 AS t2
LEFT JOIN table_name_1 AS t1 ON t1.column_name = t2.column_name
WHERE t1.column_name IS NULL
Note that if the column values are constrained to be unique (PK or Unique constraint), or you don't care if duplicate values come in result, you can remove DISTINCT keyword used in the above query.
Result:
| column_name |
| ------------ |
| 555555555555 |
| 666666666666 |
View on DB Fiddle
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
edited Nov 20 at 12:13
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
answered Nov 1 at 11:25
Madhur Bhaiya
19.3k62236
19.3k62236
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:58
@AhmedMaher do you have any indexing defined on the tables ?
– Madhur Bhaiya
Nov 20 at 12:01
No, both tables are 1 column tables and that column is the primary one.
– Ahmed Maher
Nov 21 at 8:28
@AhmedMaher since the column is Primary Key. You can remove theDISTINCTfrom my query and try again.
– Madhur Bhaiya
Nov 21 at 8:52
add a comment |
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:58
@AhmedMaher do you have any indexing defined on the tables ?
– Madhur Bhaiya
Nov 20 at 12:01
No, both tables are 1 column tables and that column is the primary one.
– Ahmed Maher
Nov 21 at 8:28
@AhmedMaher since the column is Primary Key. You can remove theDISTINCTfrom my query and try again.
– Madhur Bhaiya
Nov 21 at 8:52
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:58
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:58
@AhmedMaher do you have any indexing defined on the tables ?
– Madhur Bhaiya
Nov 20 at 12:01
@AhmedMaher do you have any indexing defined on the tables ?
– Madhur Bhaiya
Nov 20 at 12:01
No, both tables are 1 column tables and that column is the primary one.
– Ahmed Maher
Nov 21 at 8:28
No, both tables are 1 column tables and that column is the primary one.
– Ahmed Maher
Nov 21 at 8:28
@AhmedMaher since the column is Primary Key. You can remove the
DISTINCT from my query and try again.– Madhur Bhaiya
Nov 21 at 8:52
@AhmedMaher since the column is Primary Key. You can remove the
DISTINCT from my query and try again.– Madhur Bhaiya
Nov 21 at 8:52
add a comment |
up vote
0
down vote
Your query should work with this. Check fiddle
SELECT DISTINCT column_name
FROM table_name_2
WHERE column_name NOT IN (SELECT column_name
FROM table_name_1);
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
Also as the other solution, both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
add a comment |
up vote
0
down vote
Your query should work with this. Check fiddle
SELECT DISTINCT column_name
FROM table_name_2
WHERE column_name NOT IN (SELECT column_name
FROM table_name_1);
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
Also as the other solution, both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
add a comment |
up vote
0
down vote
up vote
0
down vote
Your query should work with this. Check fiddle
SELECT DISTINCT column_name
FROM table_name_2
WHERE column_name NOT IN (SELECT column_name
FROM table_name_1);
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
Your query should work with this. Check fiddle
SELECT DISTINCT column_name
FROM table_name_2
WHERE column_name NOT IN (SELECT column_name
FROM table_name_1);
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
answered Nov 1 at 11:32
mbharanidharan88
4,04732354
4,04732354
Also as the other solution, both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
add a comment |
Also as the other solution, both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Also as the other solution, both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Also as the other solution, both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
add a comment |
up vote
0
down vote
So some records from the second table are needed.
Then the second table must be in the outer query.
The (NOT) EXISTS criteria is different from (NOT) IN.
When using an EXISTS, the relation between the query in the EXISTS and the outer query needs to be added inside the EXISTS.
SELECT DISTINCT column_name
FROM `table_name_2` AS t2
WHERE NOT EXISTS (
SELECT 1
FROM `table_name_1` AS t1
WHERE t1.column_name = t2.column_name
);
SqlFiddle test here
answered Nov 1 at 11:33
LukStorms
11.2k31532
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Then I assume that there's no index ontable_name_1.column_name. I wouldn't know about why there's no result from your real tables. Maybe t1 has a matching column_name in t2 for all?
– LukStorms
Nov 20 at 12:08
@AhmedMaher One other trick you could try is using a temporary table with a primary key on column_name. Then insert the unique values to it. F.e.insert into tmpTable (column_name) select column_name from table_name_1 where column_name is not null group by column_name. Then change the query so it uses the temporary table instead. It could be faster because it could have less records, and it would be indexed on the field you try to match.
– LukStorms
Nov 20 at 13:25
There are results, I know that. Both tables are 1 column tables and that column is the primary one.What about altering both table and adding a separate primary key column? Would that help?
– Ahmed Maher
Nov 21 at 8:29
@AhmedMaher I kinda assumed that your real tables would have more columns and that you simplified the issue for your question. Then why not just make that one column the primary key in both tables? You can't insert dups in it afterwards though. Also that would open up another possible solution : the MINUS operator.
– LukStorms
Nov 21 at 8:45
|
show 2 more comments
up vote
0
down vote
So some records from the second table are needed.
Then the second table must be in the outer query.
The (NOT) EXISTS criteria is different from (NOT) IN.
When using an EXISTS, the relation between the query in the EXISTS and the outer query needs to be added inside the EXISTS.
SELECT DISTINCT column_name
FROM `table_name_2` AS t2
WHERE NOT EXISTS (
SELECT 1
FROM `table_name_1` AS t1
WHERE t1.column_name = t2.column_name
);
SqlFiddle test here
answered Nov 1 at 11:33
LukStorms
11.2k31532
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Then I assume that there's no index ontable_name_1.column_name. I wouldn't know about why there's no result from your real tables. Maybe t1 has a matching column_name in t2 for all?
– LukStorms
Nov 20 at 12:08
@AhmedMaher One other trick you could try is using a temporary table with a primary key on column_name. Then insert the unique values to it. F.e.insert into tmpTable (column_name) select column_name from table_name_1 where column_name is not null group by column_name. Then change the query so it uses the temporary table instead. It could be faster because it could have less records, and it would be indexed on the field you try to match.
– LukStorms
Nov 20 at 13:25
There are results, I know that. Both tables are 1 column tables and that column is the primary one.What about altering both table and adding a separate primary key column? Would that help?
– Ahmed Maher
Nov 21 at 8:29
@AhmedMaher I kinda assumed that your real tables would have more columns and that you simplified the issue for your question. Then why not just make that one column the primary key in both tables? You can't insert dups in it afterwards though. Also that would open up another possible solution : the MINUS operator.
– LukStorms
Nov 21 at 8:45
|
show 2 more comments
up vote
0
down vote
up vote
0
down vote
So some records from the second table are needed.
Then the second table must be in the outer query.
The (NOT) EXISTS criteria is different from (NOT) IN.
When using an EXISTS, the relation between the query in the EXISTS and the outer query needs to be added inside the EXISTS.
SELECT DISTINCT column_name
FROM `table_name_2` AS t2
WHERE NOT EXISTS (
SELECT 1
FROM `table_name_1` AS t1
WHERE t1.column_name = t2.column_name
);
SqlFiddle test here
answered Nov 1 at 11:33
LukStorms
11.2k31532
So some records from the second table are needed.
Then the second table must be in the outer query.
The (NOT) EXISTS criteria is different from (NOT) IN.
When using an EXISTS, the relation between the query in the EXISTS and the outer query needs to be added inside the EXISTS.
SELECT DISTINCT column_name
FROM `table_name_2` AS t2
WHERE NOT EXISTS (
SELECT 1
FROM `table_name_1` AS t1
WHERE t1.column_name = t2.column_name
);
SqlFiddle test here
answered Nov 1 at 11:33
LukStorms
11.2k31532
edited Nov 1 at 11:57
answered Nov 1 at 11:33
LukStorms
11.2k31532
answered Nov 1 at 11:33
LukStorms
11.2k31532
answered Nov 1 at 11:33
LukStorms
11.2k31532
11.2k31532
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Then I assume that there's no index ontable_name_1.column_name. I wouldn't know about why there's no result from your real tables. Maybe t1 has a matching column_name in t2 for all?
– LukStorms
Nov 20 at 12:08
@AhmedMaher One other trick you could try is using a temporary table with a primary key on column_name. Then insert the unique values to it. F.e.insert into tmpTable (column_name) select column_name from table_name_1 where column_name is not null group by column_name. Then change the query so it uses the temporary table instead. It could be faster because it could have less records, and it would be indexed on the field you try to match.
– LukStorms
Nov 20 at 13:25
There are results, I know that. Both tables are 1 column tables and that column is the primary one.What about altering both table and adding a separate primary key column? Would that help?
– Ahmed Maher
Nov 21 at 8:29
@AhmedMaher I kinda assumed that your real tables would have more columns and that you simplified the issue for your question. Then why not just make that one column the primary key in both tables? You can't insert dups in it afterwards though. Also that would open up another possible solution : the MINUS operator.
– LukStorms
Nov 21 at 8:45
|
show 2 more comments
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Then I assume that there's no index ontable_name_1.column_name. I wouldn't know about why there's no result from your real tables. Maybe t1 has a matching column_name in t2 for all?
– LukStorms
Nov 20 at 12:08
@AhmedMaher One other trick you could try is using a temporary table with a primary key on column_name. Then insert the unique values to it. F.e.insert into tmpTable (column_name) select column_name from table_name_1 where column_name is not null group by column_name. Then change the query so it uses the temporary table instead. It could be faster because it could have less records, and it would be indexed on the field you try to match.
– LukStorms
Nov 20 at 13:25
There are results, I know that. Both tables are 1 column tables and that column is the primary one.What about altering both table and adding a separate primary key column? Would that help?
– Ahmed Maher
Nov 21 at 8:29
@AhmedMaher I kinda assumed that your real tables would have more columns and that you simplified the issue for your question. Then why not just make that one column the primary key in both tables? You can't insert dups in it afterwards though. Also that would open up another possible solution : the MINUS operator.
– LukStorms
Nov 21 at 8:45
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Both tables are so large and the query took few hours running without a result.
– Ahmed Maher
Nov 20 at 11:59
Then I assume that there's no index on
table_name_1.column_name. I wouldn't know about why there's no result from your real tables. Maybe t1 has a matching column_name in t2 for all?– LukStorms
Nov 20 at 12:08
Then I assume that there's no index on
table_name_1.column_name. I wouldn't know about why there's no result from your real tables. Maybe t1 has a matching column_name in t2 for all?– LukStorms
Nov 20 at 12:08
@AhmedMaher One other trick you could try is using a temporary table with a primary key on column_name. Then insert the unique values to it. F.e.
insert into tmpTable (column_name) select column_name from table_name_1 where column_name is not null group by column_name. Then change the query so it uses the temporary table instead. It could be faster because it could have less records, and it would be indexed on the field you try to match.– LukStorms
Nov 20 at 13:25
@AhmedMaher One other trick you could try is using a temporary table with a primary key on column_name. Then insert the unique values to it. F.e.
insert into tmpTable (column_name) select column_name from table_name_1 where column_name is not null group by column_name. Then change the query so it uses the temporary table instead. It could be faster because it could have less records, and it would be indexed on the field you try to match.– LukStorms
Nov 20 at 13:25
There are results, I know that. Both tables are 1 column tables and that column is the primary one.What about altering both table and adding a separate primary key column? Would that help?
– Ahmed Maher
Nov 21 at 8:29
There are results, I know that. Both tables are 1 column tables and that column is the primary one.What about altering both table and adding a separate primary key column? Would that help?
– Ahmed Maher
Nov 21 at 8:29
@AhmedMaher I kinda assumed that your real tables would have more columns and that you simplified the issue for your question. Then why not just make that one column the primary key in both tables? You can't insert dups in it afterwards though. Also that would open up another possible solution : the MINUS operator.
– LukStorms
Nov 21 at 8:45
@AhmedMaher I kinda assumed that your real tables would have more columns and that you simplified the issue for your question. Then why not just make that one column the primary key in both tables? You can't insert dups in it afterwards though. Also that would open up another possible solution : the MINUS operator.
– LukStorms
Nov 21 at 8:45
|
show 2 more comments
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53099904%2fcompare-two-mysql-tables-and-select-distinct-entries-in-second-one%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Please provide a relevant and minimal sample data showcasing your requirements, and expected output. Refer this link on how to frame a good SQL question: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Madhur Bhaiya
Nov 1 at 11:02
SELECT DISTINCT column_name FROM
table_name_2WHERE NOT EXISTS (SELECT column_name FROMtable_name_1);– mbharanidharan88
Nov 1 at 11:26