joins in sql

SQL Joins -: Display data from multiple tables in sql called the joins. In other words we can say when data from more then one table in database is required a join condition is use there. In one table row is Joined to another table row according to common value existing in corresponding column. as like

Select Table 1. column, table 2 .column from table 1,table 2, where table 1. column 1= column 2; 

In SQL Joins clause is used to combine records from two or more tables in a database. A Join means for combining fields from two tables by using values common to each.

When we writing a SELECT statement that  joins tables,precede the column name with the table name for clarity and to enhance database access.

If the same column name appears in more then one table the column name must be prefixed with the table name.

Example — suppose we have two table first is EMP and second is dept.

Select ename, dname from emp, dept;

 Types of Joining 

There are Two types of join  condition . these are

  1. Equijoin – This is called the inner Join or simple joins.
  2. Non Equijoin .

Addition join method are  also using in sql these are the

  1. Outer join
  2. Self join
  3. set operators

Inner Joins -:  For understanding the inner Joins we take a example as like There are two tables and name are EMP and dept. Hear are the emp table 

Two table Joining -:

Another table is  dept.

For inner joining  both table we select this command

Select emp.empNo, emp.name, emp.designation, dept.dname from emp.dept where emp.deptno=dept.dept no;

There are some rules for inner Joining these are ..

  • Table alias can be up to 30 character in length but the shorter they are better.
  • If a table alias is used for a particular table name in the form clause, then the table alias must be substituted for the table name throughout the Select statement.
  • Table alias should be meaningfully.
  • The table alias is valid only for current Select statement.

Joining More then two Tables.-: Some time we need to Joining more then two tables suppose we are joining 3 table and third table name is customer. we use this query ..

Select emp.empNo, emp.name, emp.designation, dept.dname,Customer.cno, customer.cname from emp.dept where emp.deptno=dept.dept no;

Non EquiJoin -: If any operator other than an equality operator (=) is used to join the tables in the query it is Non equijoin.

Select e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal AND s.hisal;

Outer Join -: If a row does not satisfied a join condition , the row will not appear in the query result for example in the equation condition of EMP and Dept table department operation does not appear because no one works in that .

The Missing row can be returned if any outer Join Operator is used in the join condition. The operator is a plus sign enclosed in parentheses (+) and it is placed on the side of the join that is deficient in information .This operator has the effect of creating one or more null rows, to which one or more rows from non deficient table can be joined.

Select e.empNo, e.name,  d.dname from emp e, dept d where emp.deptno(+)=dept.dept no, order by e.deptno;

Outer Join Restrictions-: The outer join operator can appear on only one side of the expression the side that has information missing. It returns those rows from one table that has no direct match in the other table.

Select e.empNo, e.name,  d.dname from emp e, dept d where d.deptno= e.deptno(+) or e.salary>5000;

Self Joins–:Sometime we need to join itself then we are using self join. for example a manager need to find every employee name

select worker.ename !!’ Work in ‘!! manager. ename from emp worker emp manager where worker.mgr=manager.empno;

 

Leave a Comment