Outer Joins - Revision history

Bacchas at 15:28, 1 November 2017

2017-11-01T15:28:03Z

← Older revision		Revision as of 15:28, 1 November 2017
Line 36:		Line 36:
	WHERE A.product_id = B.product_id(+) ;		WHERE A.product_id = B.product_id(+) ;

			=== Full Outer Join ===
			''' Returns all rows for the LEFT and RIGHT table with NULLS in place where the join condition is not met '''

	A ~~full join can’t be used with the outer join operator; it can be used only with the~~ JOIN ~~keyword~~.		SELECT A.sales_date, A.order_id, A.product_id, B.product_name
			FROM sales A FULL OUTER JOIN product B
			ON A.product_id = B.product_id ;



	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 14:19, 1 November 2017

2017-11-01T14:19:39Z

← Older revision		Revision as of 14:19, 1 November 2017
Line 19:		Line 19:
	===LEFT OUTER JOIN ===		===LEFT OUTER JOIN ===
	''' The left Outer join will provide all the customers form the customers table and if there is no order in the order table it will display as a null '''		''' The left Outer join will provide all the customers form the customers table and if there is no order in the order table it will display as a null '''
	''' This type of join returns all rows ~~fro~~ the LEFT hand table specified in the ON condition and ONLY those rows from the other table where ~~teh~~ joined fields are equal(join condition is met)'''
			''' This type of join returns all rows froM the LEFT hand table specified in the ON condition and ONLY those rows from the other table where the joined fields are equal(join condition is met). if there are no match on table 2 then it will have a null value'''

	SELECT c.lastname, c.firstname, o.order#		SELECT c.lastname, c.firstname, o.order#
Line 25:		Line 26:
	USING (customer#)		USING (customer#)
	ORDER BY c.lastname, c.firstname		ORDER BY c.lastname, c.firstname

			SELECT A.sales_date, A.order_id, A.product_id, B.product_name
			FROM sales A LEFT JOIN product B
			ON A.product_id = B.product_id ;

			''' ORACLE SYNTAX '''
			SELECT A.sales_date, A.order_id, A.product_id, B.product_name
			FROM sales A , product B
			WHERE A.product_id = B.product_id(+) ;

Bacchas at 14:11, 1 November 2017

2017-11-01T14:11:46Z

← Older revision		Revision as of 14:11, 1 November 2017
Line 17:		Line 17:
	table is deficient when matching rows. (That is, it performs a combination of left and right		table is deficient when matching rows. (That is, it performs a combination of left and right
	outer joins.)		outer joins.)
			===LEFT OUTER JOIN ===
			''' The left Outer join will provide all the customers form the customers table and if there is no order in the order table it will display as a null '''
			''' This type of join returns all rows fro the LEFT hand table specified in the ON condition and ONLY those rows from the other table where teh joined fields are equal(join condition is met)'''

	SELECT c.lastname, c.firstname, o.order#		SELECT c.lastname, c.firstname, o.order#
	FROM customers c LEFT OUTER JOIN orders o		FROM customers c LEFT OUTER JOIN orders o
	USING (customer#)		USING (customer#)
	ORDER BY c.lastname, c.firstname		ORDER BY c.lastname, c.firstname


	A full join can’t be used with the outer join operator; it can be used only with the JOIN keyword.		A full join can’t be used with the outer join operator; it can be used only with the JOIN keyword.

	~~''' The left Outer join will provide all the customers form the customers table and if there is no order in the order table it will display as a null '''~~

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas: /* Outer Joins: JOIN Method */

2017-10-25T23:02:12Z

Outer Joins: JOIN Method

← Older revision		Revision as of 23:02, 25 October 2017
Line 23:		Line 23:
	A full join can’t be used with the outer join operator; it can be used only with the JOIN keyword.		A full join can’t be used with the outer join operator; it can be used only with the JOIN keyword.

			''' The left Outer join will provide all the customers form the customers table and if there is no order in the order table it will display as a null '''

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 01:04, 24 October 2017

2017-10-24T01:04:39Z

← Older revision		Revision as of 01:04, 24 October 2017
Line 21:		Line 21:
	USING (customer#)		USING (customer#)
	ORDER BY c.lastname, c.firstname		ORDER BY c.lastname, c.firstname
			A full join can’t be used with the outer join operator; it can be used only with the JOIN keyword.


	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 01:00, 24 October 2017

2017-10-24T01:00:44Z

← Older revision		Revision as of 01:00, 24 October 2017
Line 17:		Line 17:
	table is deficient when matching rows. (That is, it performs a combination of left and right		table is deficient when matching rows. (That is, it performs a combination of left and right
	outer joins.)		outer joins.)
			SELECT c.lastname, c.firstname, o.order#
			FROM customers c LEFT OUTER JOIN orders o
			USING (customer#)
			ORDER BY c.lastname, c.firstname

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 00:58, 24 October 2017

2017-10-24T00:58:12Z

← Older revision		Revision as of 00:58, 24 October 2017
Line 2:		Line 2:

	'''An outer join can be created by using the WHERE clause with an outer join operator or the OUTER JOIN keywords'''		'''An outer join can be created by using the WHERE clause with an outer join operator or the OUTER JOIN keywords'''
	===limitations when using the traditional approach to outer joins:===		==limitations when using the traditional approach to outer joins:==

	* The outer join operator can be used for only one table in the joining condition.In other words, you can’t create NULL rows in both tables at the same time.		* The outer join operator can be used for only one table in the joining condition.In other words, you can’t create NULL rows in both tables at the same time.
	* A condition that includes the outer join operator can’t use the IN or ORoperator.		* A condition that includes the outer join operator can’t use the IN or ORoperator.

			==Outer Joins: JOIN Method==
			When creating a traditional outer join with the outer join operator, the join can be applied
			to only one table—not both. However, with the JOIN keyword, you can specify which table
			the join should be applied to by using a left, right, or full outer join. Left and right outer joins
			specify which table the outer join should be applied to, based on the table’s location in the
			join condition. For example, a left outer join instructs Oracle to keep any rows in the table
			listed on the left side of the join condition, even if no matches are found with the table listed
			on the right. A full outer join keeps all rows from both tables in the results, no matter which
			table is deficient when matching rows. (That is, it performs a combination of left and right
			outer joins.)

	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas at 00:56, 24 October 2017

2017-10-24T00:56:36Z

← Older revision		Revision as of 00:56, 24 October 2017
Line 2:		Line 2:

	'''An outer join can be created by using the WHERE clause with an outer join operator or the OUTER JOIN keywords'''		'''An outer join can be created by using the WHERE clause with an outer join operator or the OUTER JOIN keywords'''
			===limitations when using the traditional approach to outer joins:===

	* The outer join operator can be used for only one table in the joining condition.In other words, you can’t create NULL rows in both tables at the same time.		* The outer join operator can be used for only one table in the joining condition.In other words, you can’t create NULL rows in both tables at the same time.

Bacchas at 00:56, 24 October 2017

2017-10-24T00:56:05Z

← Older revision		Revision as of 00:56, 24 October 2017
Line 1:		Line 1:
	'''An outer join is created when records needs to be included in the results without having a corresponding records in the join tables. These records are matched with the NULL records so that they are included in the output'''		'''An outer join is created when records needs to be included in the results without having a corresponding records in the join tables. These records are matched with the NULL records so that they are included in the output'''

			'''An outer join can be created by using the WHERE clause with an outer join operator or the OUTER JOIN keywords'''

			* The outer join operator can be used for only one table in the joining condition.In other words, you can’t create NULL rows in both tables at the same time.
			* A condition that includes the outer join operator can’t use the IN or ORoperator.


	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

Bacchas: Created page with "'''An outer join is created when records needs to be included in the results without having a corresponding records in the join tables. These records are matched with the NULL..."

2017-10-24T00:48:56Z

Created page with "'''An outer join is created when records needs to be included in the results without having a corresponding records in the join tables. These records are matched with the NULL..."

New page

'''An outer join is created when records needs to be included in the results without having a corresponding records in the join tables. These records are matched with the NULL records so that they are included in the output'''

[[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]