In equi join we can join two or mare tables using join condition.
For example the following query returns empno,name,sal,deptno and department name and city from dept,emp tables.
select emp.empno,emp.ename,emp.sal,emp.deptno,dept.dname,dept.city from emp,dept where emp.deptno=dept.deptno;
An equal sign (=) is used as comparison operator in the where clause to refer equality.
Subquery or Inner query or Nested query is a query in a query. SQL subquery is usually added in the WHERE Clause of the SQL statement. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but do not know the exact value in the database.
SELECT * FROM emp WHERE deptno = (SELECT deptno FROM dept);
Here we can get the data from emp table if the deptno is available in dept.But we can't get data from dept table.
In most cases
JOINs are faster than sub-queries and it is very rare for a sub-query to be faster.
JOINs RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.
The good thing in sub-queries is that they are more readable than
JOINs: that's why most new SQL people prefer them; it is the easy way; but when it comes to performance, JOINS are better in most cases even though they are not hard to read too.