CODEWITHSUNDEEP
mysqlsqljoinsubqueryinner-join
Viktor
Viktor

Reputation: 547

MySQL Query Inner Join Inverse?

How can we show the 'inverse' of the Inner Join. For example, I have a list of actual transactions of customers that went thru the payment processor, in this case 'Paypal' but they never clicked the 'Back to Merchant' tab so that we can process their userid and password.

This script shows ALL the people that are in the customer list and their associated place in the users database:

SELECT
`Transactions List`.`Customer Email`,
users.Email,
`Transactions List`.`Transaction ID`,
users.`Name`,
users.Phone
FROM
`Transactions List`
INNER JOIN users ON `Transactions List`.`Customer Email` = users.Email

What I'm seeking to do is show the INVERSE of that. i.e. all the people who LOST their way. They DO appear in the TRANSACTIONS LIST table but do NOT appear in the USERS table.

Anyone have an idea how to convert this MYSQL Query into the Inverse so we can quickly identify which customers did not get user accounts?

There's an existing post "Inner join inverse Php MySQL" here that wasn't answered that asks a similar question. Perhaps the person asking the question was not clear enough: Inner join inverse Php mysql

also What is the difference between “INNER JOIN” and “OUTER JOIN”? What is the difference between "INNER JOIN" and "OUTER JOIN"?

but neither of these methods actually do what I want the script to do.

Upvotes: 0

Views: 319

Answers (2)

Viktor
Viktor

Reputation: 547

select t.*
from `Transactions List` t
left join users u on t.`Customer Email` = u.email
where u.email is null

Given the above syntax and the name of the table in the database as specified above this is the correct answer. Thank you to GMB for answering the question. For other readers, keep in mind that if your database tables include spaces in their names or field names then you must use the scare quotes to identify your table or field names. This is commonly used when importing tables into MySQL from 3rd party tools.

Upvotes: 0

GMB
GMB

Reputation: 222442

What I'm seeking to do is show [...] all the people who [...] appear in the TRANSACTIONS LIST table but do NOT appear in the USERS table.

You could use not exists:

select t.*
from transactions_list t
where not exists (
    select 1 from users u where t.customer_email = u.email
)

Another way to phrase this is to use an anti-left join (this is more in the spirit of your question, that relates to joins):

select t.*
from transactions_list t
left join users u on t.customer_email = u.email
where u.email is null

This means: try to join each transaction with a user, and filter on those that did not match.

Upvotes: 5

Related Questions