Some Sql Queries 1 (NOT EXISTS,WITH)

NOT EXISTS 

select department_name
from departments outer
where not exists (select '23523532'
from employees
where department_id=outer.department_id);


In here,this query is written regarding the HR Sample Schemas.

I just wanted to use 'not exists function' to understand how it works.

In here query shows the deparments names' which were not used in 'employees' tables.

'not exists' function finds the appropriate coloumn in 'employees' table itself whatever it is. In subquery there is number '23523532',it does not count anything. Here, anything can be written in here. As if the function itself finds whatever needed and sort out the expected result.


WITH ...

1-)
In this example, in memory we create a table which normally does not exist but in memory and we put its name dept_costs having two columns
department_name, dept_total (SUM(e.salary)) with a subquery joinging two different tables which exist in HR database(employees, departments)

2-)
At this part, we create another table with a column named 'dept_avg' in memory from previous table's columns which was also created in memory.

3-)
In this part we sort out a result with these two tables created in memory.