Web Administration > Databases

joins in Database

(1/3) > >>

How many types of joins are in database? What are its effects? I need detail help in this sense.

Following are types of SQL joins:-
Join-returns both the tables if their is atleast one match in database of two tabels
Left Join-returns all the left table data even if their is no match with right table
Right Join-returns all the  right table rows only ,even when their is no match in left table rows
Full Join-returns a row when their is match in one table

There are different types of joins like inner join, outer join, self join, cross join, natural join, equi join, non-equi join.

A join is a query that combines rows from two or more tables, views, or materialized views.

Joins in Oracle:

* Equi Join: An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns.
* Self Join: A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.
* Cartesian Products: If two tables in a join query have no join condition, then Oracle Database returns their Cartesian product.
* Inner Join: An inner join (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition.
* Outer Join: An outer join extends the result of a simple join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition.
* Anti Join: An antijoin returns rows from the left side of the predicate for which there are no corresponding rows on the right side of the predicate. It returns rows that fail to match (NOT IN) the subquery on the right side.
* Semi Join: A semijoin returns rows that match an EXISTS subquery without duplicating rows from the left side of the predicate when multiple rows on the right side satisfy the criteria of the subquery.
Joins in SQL Server:

* Inner Join: Return rows when there is at least one match in both tables.
* Left Join: Return all rows from the left table, even if there are no matches in the right table.
* Right Join: Return all rows from the right table, even if there are no matches in the left table.
* Full Join: Return rows when there is a match in one of the tables.Joins in PHP:

* CROSS JOIN: This type of join is the simplest join. The cross join result in cartesian product of all the records from two tables.
* INNER JOIN OR EQUI JOIN: This is the type of join where tables are combined based on a common column.
* OUTER JOIN: Join is used to combine all rows of one table with  matching rows from the other table and also show unmatchable records from other table. It is used whenever multiple tables must be accessed through a SQL SELECT statement

 Join is a relationship between two table or more tables.There are mainly 4 types of Join-
1- Self Join
2- Inner Join
3-Outer join
4-Cross Join


[0] Message Index

[#] Next page

Go to full version