测试查询中是否存在MySQL行

Question

I am trying to create an SQL query that compares two databases with each other, however also will not result in a query failure if one of the database rows has not been created elsewhere yet.

Here's what I've gotten so far (a bit of pseudo code)

$Query="SELECT * FROM campaigns,statistics WHERE statistics.dataDate = '$todaysDate' OR statistics.dataDate DOES NOT EXIST AND where campaigns.campaignName = 'example'";

The "DOES NOT EXIST" part is where I need the help, how can I create a query that allows the query to continue with more checks if the row has not been created yet?

Thanks very much for any suggestions! I've taken a look around and can't find a solution that checks if the row doesn't exist inside of the query (will be needed for when the query is built out a bit further)

Thanks again,

Ash

edit:

To clear up any confusion here's a second example:

`$Query="SELECT * FROM campaigns,statistics WHERE campaigns.campaignName = '$randomVar' AND (campaigns.budget > statistics.budget OR statistics.budget DOES NOT EXIST )";`

I have created this example showing that if the budget does not exist (we would treat it as 0) and still return the results because the budget field hasn't been created yet elsewhere in the platform. If I run the query without this extra "OR" it will look something like this:

    `$Query="SELECT * FROM campaigns,statistics WHERE campaigns.campaignName = '$randomVar' AND (campaigns.budget > statistics.budget)";`

The problem with this is that in my platform so far the statistics.budget is not always created before this query is ran, therefor if it doesn't have a row I'm happy for the query to treat it as 0

Thanks!

Ash

Answer 1

Based on this example you provide:

SELECT *
FROM campaigns,statistics
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR statistics.budget DOES NOT EXIST)

First, explicitly define your JOIN:

SELECT *
FROM
    campaigns
    LEFT OUTER JOIN statistics
        ON campaigns.someColumn = statistics.someColumn
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR statistics.budget DOES NOT EXIST)

(Or whatever relational join makes sense for these tables.) Explicit joins are going to take a lot of the guesswork out of how your data is related.

If you want records where statistics.budget is NULL then you would do something like this:

SELECT *
FROM
    campaigns
    LEFT OUTER JOIN statistics
        ON campaigns.someColumn = statistics.someColumn
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR statistics.budget IS NULL)

Or if you want to further narrow it to only those records where campaigns.budget has a value at all, something like this:

SELECT *
FROM
    campaigns
    LEFT OUTER JOIN statistics
        ON campaigns.someColumn = statistics.someColumn
WHERE
    campaigns.campaignName = '$randomVar'
    AND (campaigns.budget > statistics.budget
         OR (statistics.budget IS NULL
             AND campaigns.budget IS NOT NULL))

When a value in a record "does not exist" that means the value is NULL. So you can use IS NULL and IS NOT NULL to check for the presence/absence of that case.

---

we would treat it as 0

That would take place in the SELECT clause. First, explicitly define the columns you want:

SELECT
    campaigns.aColumn,
    campaigns.anotherColumn,
    statistics.someOtherColumn

... and so on. Once you have that, you can define a clause to return a given column as a given value if it meets a certain condition. For example, you can return statistics.budget as-is if it isn't NULL, or 0 if it is. Something like this:

SELECT
    campaigns.aColumn,
    campaigns.anotherColumn,
    statistics.someOtherColumn,
    IFNULL(statistics.budget, 0) AS budget

---

Note: This is a SQL injection problem waiting to happen:

WHERE campaigns.campaignName = '$randomVar'

Use query parameters to treat values as values instead of inserting strings directly into the query as executable code.