MySQL Query Inner Join Inverse?

Question

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.

Putting words in scare quotes does not make clear the idiosyncratic specific meaning that you didn't write out. If you don't phrase things clearly you can't communicate, reason or search. When clear this will be a faq. Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. And what parts can you do? [mre] — philipxy, Feb 22 '20 at 21:46
I pasted in the exact code that was generated by the MySQL software exactly as is. The 'scare quotes' are there because when the table was imported from CSV it had spaces in the name of the table instead of underscores. I provided the exact syntax that works given the structure of the import. I appreciate your direction on one phrasing as a title - what would be helpful is an example of how this exact same post could be written more succinctly so that in the future I know what are the best practices. — Viktor, Feb 22 '20 at 22:10
The scare quotes I am talking about are around "inverse" in "How can we show the 'inverse' of the Inner Join". Similarly CAPITALIZING DOESN'T CLARIFY. What I meant by parts is parts of your goal of "inverting". (Note a [mre] includes input & result.) Re faqs, google your own phrase here without particular strings: 'They DO appear in one table but do NOT appear in another table'. I don't know if that's what you actually want but it's going to immediately hit many very old very upvoted basic Qs&As--verbatim for answers here--answers that shouldn't be posted while this is unclear or a duplicate. — philipxy, Feb 22 '20 at 22:19

score 4 · Accepted Answer · answered Feb 22 '20 at 21:30

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.

score 0 · Answer 2 · answered Feb 22 '20 at 22:17

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.

MySQL Query Inner Join Inverse?

2 Answers2