joint

vinodh K

hi, anybody can tell me about joints Thanks & Regards vinodh

Blue_Boy

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post. www.cacttus.com

Lost User

vinodh.K wrote:

anybody can tell me about joints

After smoking one everything seems very mellow.

Unrequited desire is character building. OriginalGriff

Pete OHanlon

I assume you mean joins. Basically they are used to join tables together when some underlying criteria is satisfied. There are, effectively, three types - LEFT JOINS, RIGHT JOINS and equi-joins (otherwise known as the standard JOIN).

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Mycroft Holmes

Read this article[^]

Never underestimate the power of human stupidity RAH

Tim Carmichael

We occasionally use cross joins as well.

S Douglas

vinodh.K wrote: anybody can tell me about joints Assuming you really meant SQL Joins There are six, types of joins. 1: Left join Return all records from the left most table on the join.

SELECT 
	e.\*
	, a.\*
FROM employee AS e (Notice the AS, that creates an alias to that table)
	LEFT OUTER JOIN Address a (e.EmployeeKey = a.EmployeeKey)

Returns all of the records in the employees table, and only those from the address table that actualy match 2: Right join Return all records from the right most table on the join.

SELECT 
	e.\*
	, a.\*
FROM employee AS e (Notice the AS, that creates an alias to that table)
	RIGHT OUTER JOIN Address a (e.EmployeeKey = a.EmployeeKey)

Returns all of the records in the Adreess table, and only those from the employees table that actualy match 3: Full Join Returns all rows from both Tables

SELECT 
	e.\*
	, a.\*
FROM employee AS e (Notice the AS, that creates an alias to that table)
	FULL JOIN Address a (e.EmployeeKey = a.EmployeeKey)

4: Inner join Return only records where both keys match exactly.

SELECT 
	e.\*
	, a.\*
FROM employee AS e (Notice the AS, that creates an alias to that table)
	INNER OUTER JOIN Address a (e.EmployeeKey = a.EmployeeKey)

Returns all of the Employes and Adresses where both keys match exactly 5: Cross Join Use this with caution, all records on all sides are paired and returned

SELECT 
	e.\*
	, a.\*
FROM employee AS e (Notice the AS, that creates an alias to that table)
	CROSS JOIN Address a (e.EmployeeKey = a.EmployeeKey)

This is the most expensive join posible, as for every record on both sides are returned, so if you had two tables that had ten rows in each, 100 rows would be returned. 6 Self Join

SELECT 
	e1.\*
	, e2.\*
FROM employee AS e1 (Notice the AS, that creates an alias to that table)
	JOIN employee e2 (e1.EmployeeKey = e2.EmployeeKey)

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

Ganu Sharma

With Oracle, we are used to joining datasets by combinations of datasources in the FROM clause and a WHERE clause to join them together. Datasets are usually tables, but can also be views, in-inline views, subqueries, table functions, nested tables and so on. Oracle join syntax is generally as follows: SELECT ... FROM dataset_one d1 , dataset_two d2 WHERE d1.column(s) = d2.column(s) AND ... With this syntax we separate datasources by commas and code a single WHERE clause that will include the join predicates together with any filter predicates we might require. ANSI join syntax is slightly different on two counts. First, we specify the type of join we require and second we separate the join predicates from the filter predicates. ASNI syntax can notionally be expressed as follows: SELECT ... FROM dataset_one d1 JOIN TYPE dataset_two d2 ON (d1.column(s) = d2.column(s)) --<-- can also use USING (column(s)) WHERE filter_predicates... As commented, the ON clause is where we specify our joins. If the column names are the same, we can replace this with a USING clause. We will see examples of both methods for expressing join predicates throughout this article. Given this pseudo-syntax, we will examples of the following join types in this article. * INNER JOIN * NATURAL JOIN * CROSS JOIN * LEFT OUTER JOIN * RIGHT OUTER JOIN * FULL OUTER JOIN inner join When we join two tables or datasets together on an equality (i.e. column or set of columns) we are performing an inner join. The ANSI method for joining EMP and DEPT is as follows. SQL> SELECT d.dname, d.loc, e.ename, e.job 2 FROM dept d 3 INNER JOIN 4 emp e 5 USING (deptno); DNAME LOC ENAME JOB -------------- ------------- ---------- --------- RESEARCH DALLAS SMITH CLERK SALES CHICAGO ALLEN SALESMAN SALES CHICAGO WARD SALESMAN RESEARCH DALLAS JONES MANAGER SALES CHICAGO MARTIN SALESMAN SALES CHICAGO BLAKE MANAGER ACCOUNTING NEW YORK CLARK MANAGER RESEARCH DALLAS SCOTT ANALYST ACCOUNTING NEW YORK KING PRESIDENT SALES CHICAGO TURNER SALESMAN RESEARCH DALLAS ADAMS CLERK SALES CHICAGO JAMES CLERK RESEARCH DALLAS FORD ANALYST ACCOUNTING NEW YORK MILLER CLERK