Menu
can someone help me write sql for a scernerio like this:
Table 1 2 columns: ID, Name Table 2 2 columns: ID, Name I want a query to show names from Table 1 that are not in table 2. So filter out all the names in table 1 that are in table 2 is the result query. Use ID for the filtering not name.
This will help me in what I am trying to do. Thanks in advance
625
Answers. You can use Except and Not Exists and LEFT OUTER JOIN.
There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN.
Different Types of SQL JOINs (INNER) JOIN : Returns records that have matching values in both tables. LEFT (OUTER) JOIN : Returns all records from the left table, and the matched records from the right table. RIGHT (OUTER) JOIN : Returns all records from the right table, and the matched records from the left table.
Basically, we have only three types of joins: Inner join, Outer join, and Cross join. We use any of these three JOINS to join a table to itself.
Select * from table1 left join table2 on table1.id = table2.id where table2.id is null This should perform better than the left join...is null version. See here and here for comparisons.
select t1.id, t1.name from table1 t1 where not exists(select null from table2 t2 where t2.id = t1.id) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
