Why should I use EXISTS() function in MySQL?

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

I have this query:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        EXISTS(SELECT 1 FROM t2 WHERE t2.post_id = :id)

And when I remove that EXISTS() function, still my code works:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1)

So why should I write that? What's its advantage?

edited May 25 '16 at 13:57

asked May 25 '16 at 13:53

Martin AJ

2,18021232

Exists returns on the first match. Subquery retrieves all matching records. Go read the manual.

– Pred
May 25 '16 at 13:55

@Pred Thanks for the tip. But I've edited my second query, So is there any different now?

– Martin AJ
May 25 '16 at 13:58

ANSI SQL compliance is one reason.

– jarlh
May 25 '16 at 13:58

3

Typing EXISTS hurts, but LIMIT 1 does not? EXISTS is semantically correct. Your second query will fail on other DBMSs, and simply a bad practice. If you accidentally fail to use 1 and use a column name instead, and the matching record contains a NULL or 0, MySQL will cast it to FALSE and your query will provide a false result.

– Pred
May 25 '16 at 14:02

your second query like SELECT * FROM mytable t1 WHERE t1.id = :id AND 1 and this is a correct query. because 1 evaluate to true

– wajih
May 25 '16 at 14:09

|
show 1 more comment

I have this query:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        EXISTS(SELECT 1 FROM t2 WHERE t2.post_id = :id)

And when I remove that EXISTS() function, still my code works:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1)

So why should I write that? What's its advantage?

edited May 25 '16 at 13:57

asked May 25 '16 at 13:53

Martin AJ

2,18021232

Exists returns on the first match. Subquery retrieves all matching records. Go read the manual.

– Pred
May 25 '16 at 13:55

@Pred Thanks for the tip. But I've edited my second query, So is there any different now?

– Martin AJ
May 25 '16 at 13:58

ANSI SQL compliance is one reason.

– jarlh
May 25 '16 at 13:58

3

Typing EXISTS hurts, but LIMIT 1 does not? EXISTS is semantically correct. Your second query will fail on other DBMSs, and simply a bad practice. If you accidentally fail to use 1 and use a column name instead, and the matching record contains a NULL or 0, MySQL will cast it to FALSE and your query will provide a false result.

– Pred
May 25 '16 at 14:02

your second query like SELECT * FROM mytable t1 WHERE t1.id = :id AND 1 and this is a correct query. because 1 evaluate to true

– wajih
May 25 '16 at 14:09

|
show 1 more comment

I have this query:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        EXISTS(SELECT 1 FROM t2 WHERE t2.post_id = :id)

And when I remove that EXISTS() function, still my code works:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1)

So why should I write that? What's its advantage?

edited May 25 '16 at 13:57

asked May 25 '16 at 13:53

Martin AJ

2,18021232

I have this query:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        EXISTS(SELECT 1 FROM t2 WHERE t2.post_id = :id)

And when I remove that EXISTS() function, still my code works:

SELECT * FROM mytable t1

  WHERE t1.id = :id AND

        (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1)

So why should I write that? What's its advantage?

mysql sql exists

edited May 25 '16 at 13:57

asked May 25 '16 at 13:53

Martin AJ

2,18021232

edited May 25 '16 at 13:57

asked May 25 '16 at 13:53

Martin AJ

2,18021232

edited May 25 '16 at 13:57

asked May 25 '16 at 13:53

Martin AJ

2,18021232

asked May 25 '16 at 13:53

Martin AJ

2,18021232

asked May 25 '16 at 13:53

Martin AJ

2,18021232

Exists returns on the first match. Subquery retrieves all matching records. Go read the manual.

– Pred
May 25 '16 at 13:55

@Pred Thanks for the tip. But I've edited my second query, So is there any different now?

– Martin AJ
May 25 '16 at 13:58

ANSI SQL compliance is one reason.

– jarlh
May 25 '16 at 13:58

3

Typing EXISTS hurts, but LIMIT 1 does not? EXISTS is semantically correct. Your second query will fail on other DBMSs, and simply a bad practice. If you accidentally fail to use 1 and use a column name instead, and the matching record contains a NULL or 0, MySQL will cast it to FALSE and your query will provide a false result.

– Pred
May 25 '16 at 14:02

your second query like SELECT * FROM mytable t1 WHERE t1.id = :id AND 1 and this is a correct query. because 1 evaluate to true

– wajih
May 25 '16 at 14:09

|
show 1 more comment

Exists returns on the first match. Subquery retrieves all matching records. Go read the manual.

– Pred
May 25 '16 at 13:55

@Pred Thanks for the tip. But I've edited my second query, So is there any different now?

– Martin AJ
May 25 '16 at 13:58

ANSI SQL compliance is one reason.

– jarlh
May 25 '16 at 13:58

3

Typing EXISTS hurts, but LIMIT 1 does not? EXISTS is semantically correct. Your second query will fail on other DBMSs, and simply a bad practice. If you accidentally fail to use 1 and use a column name instead, and the matching record contains a NULL or 0, MySQL will cast it to FALSE and your query will provide a false result.

– Pred
May 25 '16 at 14:02

your second query like SELECT * FROM mytable t1 WHERE t1.id = :id AND 1 and this is a correct query. because 1 evaluate to true

– wajih
May 25 '16 at 14:09

Exists returns on the first match. Subquery retrieves all matching records. Go read the manual.

– Pred
May 25 '16 at 13:55

@Pred Thanks for the tip. But I've edited my second query, So is there any different now?

– Martin AJ
May 25 '16 at 13:58

ANSI SQL compliance is one reason.

– jarlh
May 25 '16 at 13:58

Typing EXISTS hurts, but LIMIT 1 does not? EXISTS is semantically correct. Your second query will fail on other DBMSs, and simply a bad practice. If you accidentally fail to use 1 and use a column name instead, and the matching record contains a NULL or 0, MySQL will cast it to FALSE and your query will provide a false result.

– Pred
May 25 '16 at 14:02

your second query like SELECT * FROM mytable t1 WHERE t1.id = :id AND 1 and this is a correct query. because 1 evaluate to true

– wajih
May 25 '16 at 14:09

|
show 1 more comment

2 Answers
2

active

oldest

votes

In short:

EXISTS returns when it finds the first result instead of fetching all matching records (so it is more efficient when there are multiple records matching the criteria)

EXISTS is semantically correct.

When there is a column name instead of 1 in the second query, and the column contains NULL, FALSE, 0, etc, MySQL will implicitly convert it to FALSE, which leads to a false result.

EXISTS is actually defined by the ANSI standard, while the second form is not. (The second query may fail in other DBMS)

As an extra side note, you are fine with * too when you are using EXISTS, since it checks if there is a matching record, not the value.

edited Nov 17 '18 at 6:35

KLDavenport

557723

answered May 25 '16 at 14:15

Pred

6,87831534

add a comment |

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

And when you use ... (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1) you either return 1 on success or NULL on no thing which consider as True or False respectively.

Working with Exists is more professional because:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

It takes the best way to return True or False.

reference from MySQL Dev site

answered May 25 '16 at 14:18

wajih

2,5371233

add a comment |

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',
autoActivateHeartbeat: false,
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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f37439312%2fwhy-should-i-use-exists-function-in-mysql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

In short:

EXISTS returns when it finds the first result instead of fetching all matching records (so it is more efficient when there are multiple records matching the criteria)

EXISTS is semantically correct.

When there is a column name instead of 1 in the second query, and the column contains NULL, FALSE, 0, etc, MySQL will implicitly convert it to FALSE, which leads to a false result.

EXISTS is actually defined by the ANSI standard, while the second form is not. (The second query may fail in other DBMS)

As an extra side note, you are fine with * too when you are using EXISTS, since it checks if there is a matching record, not the value.

edited Nov 17 '18 at 6:35

KLDavenport

557723

answered May 25 '16 at 14:15

Pred

6,87831534

add a comment |

In short:

EXISTS returns when it finds the first result instead of fetching all matching records (so it is more efficient when there are multiple records matching the criteria)

EXISTS is semantically correct.

When there is a column name instead of 1 in the second query, and the column contains NULL, FALSE, 0, etc, MySQL will implicitly convert it to FALSE, which leads to a false result.

EXISTS is actually defined by the ANSI standard, while the second form is not. (The second query may fail in other DBMS)

As an extra side note, you are fine with * too when you are using EXISTS, since it checks if there is a matching record, not the value.

edited Nov 17 '18 at 6:35

KLDavenport

557723

answered May 25 '16 at 14:15

Pred

6,87831534

add a comment |

In short:

EXISTS returns when it finds the first result instead of fetching all matching records (so it is more efficient when there are multiple records matching the criteria)

EXISTS is semantically correct.

When there is a column name instead of 1 in the second query, and the column contains NULL, FALSE, 0, etc, MySQL will implicitly convert it to FALSE, which leads to a false result.

EXISTS is actually defined by the ANSI standard, while the second form is not. (The second query may fail in other DBMS)

As an extra side note, you are fine with * too when you are using EXISTS, since it checks if there is a matching record, not the value.

edited Nov 17 '18 at 6:35

KLDavenport

557723

answered May 25 '16 at 14:15

Pred

6,87831534

In short:

EXISTS returns when it finds the first result instead of fetching all matching records (so it is more efficient when there are multiple records matching the criteria)

EXISTS is semantically correct.

When there is a column name instead of 1 in the second query, and the column contains NULL, FALSE, 0, etc, MySQL will implicitly convert it to FALSE, which leads to a false result.

EXISTS is actually defined by the ANSI standard, while the second form is not. (The second query may fail in other DBMS)

As an extra side note, you are fine with * too when you are using EXISTS, since it checks if there is a matching record, not the value.

edited Nov 17 '18 at 6:35

KLDavenport

557723

answered May 25 '16 at 14:15

Pred

6,87831534

edited Nov 17 '18 at 6:35

KLDavenport

557723

edited Nov 17 '18 at 6:35

KLDavenport

557723

edited Nov 17 '18 at 6:35

KLDavenport

557723

answered May 25 '16 at 14:15

Pred

6,87831534

answered May 25 '16 at 14:15

Pred

6,87831534

answered May 25 '16 at 14:15

Pred

6,87831534

add a comment |

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

And when you use ... (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1) you either return 1 on success or NULL on no thing which consider as True or False respectively.

Working with Exists is more professional because:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

It takes the best way to return True or False.

reference from MySQL Dev site

answered May 25 '16 at 14:18

wajih

2,5371233

add a comment |

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

And when you use ... (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1) you either return 1 on success or NULL on no thing which consider as True or False respectively.

Working with Exists is more professional because:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

It takes the best way to return True or False.

reference from MySQL Dev site

answered May 25 '16 at 14:18

wajih

2,5371233

add a comment |

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

And when you use ... (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1) you either return 1 on success or NULL on no thing which consider as True or False respectively.

Working with Exists is more professional because:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

It takes the best way to return True or False.

reference from MySQL Dev site

answered May 25 '16 at 14:18

wajih

2,5371233

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.

And when you use ... (SELECT 1 FROM t2 WHERE t2.post_id = :id LIMIT 1) you either return 1 on success or NULL on no thing which consider as True or False respectively.

Working with Exists is more professional because:

Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.

It takes the best way to return True or False.

reference from MySQL Dev site

answered May 25 '16 at 14:18

wajih

2,5371233

answered May 25 '16 at 14:18

wajih

2,5371233

answered May 25 '16 at 14:18

wajih

2,5371233

answered May 25 '16 at 14:18

wajih

2,5371233

add a comment |

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky