Filter Column Data according to another Table Column | SQL | Oracle ExpressEdition Tutorials

Learn to filter a table's column data in output based on another table's column data.

How is this done?
-- Using the LIKE operator when we have to deal with strings (char/varchar/varchar2).

Here's the query:
-----------------------------------------------------------------------------------------------------------
select emp1.empid, empname, dept1.deptid, dept1.city
from dept1 inner join emp1
on(emp1.deptid = dept1.deptid)
where dept1.city in (select emp1.city from emp1)
or dept1.city like (emp1.city || '%');
-----------------------------------------------------------------------------------------------------------

Here the tables are joined using the inner join on the department IDs as they are not related by the foreign key constraint.

The LIKE Operator and the wildcard character % helps to filter the string data here.

The first section of the where clause is to filter the exact names of cities. Removing this will remove 'california' from the result.

This can be very useful to filter candidate names or locations or any string data which is present in two tables.


Hop on to learn more!
Subscribe to my channel to learn new Oracle/SQL tips and tricks to obtain a particular output.

Use the Oracle Express Edition for quick learning!

Check out my other videos:
--------------------------------------------------
Clone an Oracle table using SQL: https://youtu.be/8MICKuNoT1w

Add Primary Key to Existing Table: https://youtu.be/DgGoedEBLoU

Create an Application Workspace: https://youtu.be/Tl9hddgUSdI


Download the latest Oracle Express Edition release here:
http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

Follow me on Instagram: https://www.instagram.com/the_perfect_tutorials/

Follow me on Facebook: https://www.facebook.com/theperfecttutorials

Sound: Bensound