Set Operators - Revision history

Bacchas: /* MINUS */

2017-10-31T21:10:18Z

MINUS

← Older revision		Revision as of 21:10, 31 October 2017
Line 76:		Line 76:
	statement (customers in the ORDERS table) from the results of the first SELECT statement		statement (customers in the ORDERS table) from the results of the first SELECT statement
	(customers in the CUSTOMERS table).		(customers in the CUSTOMERS table).

			''' SUBTRACTS LIKES ITEMS FROM TABLE A FROM TABLE B '''
			EXAMPLE:
			TABLE A HAS APPLE, PEACH, BANANA
			TABLE B HAS PEACH, BANANA STRAWBERRY
			RESULTS
			APPLE
			''' BECAUSE PEACH AND BANANA IS CANCELLED OUT ALL THAT'S LEFT FROM THE FIRST TABLE IS APPLE. THE STRAWBERRY FROM THE SECOND TABLE DOES NOT MATTER BECAUSE IT'S USING THE FIRST TABLE AS THE COMPARISON TABLE.'''

	SELECT customer#		SELECT customer#

2017-10-31T21:03:11Z

INTERSECT

← Older revision		Revision as of 21:03, 31 October 2017
Line 55:		Line 55:
	an order recently and who exist in the CUSTOMERS table. In other words, only customers		an order recently and who exist in the CUSTOMERS table. In other words, only customers
	who are retrieved in both SELECTs should be in the results.		who are retrieved in both SELECTs should be in the results.

	''' RETURNS COMMON VALUES '''		''' RETURNS COMMON VALUES '''
	EXAMPLE;		EXAMPLE;

2017-10-31T21:02:54Z

INTERSECT

← Older revision		Revision as of 21:02, 31 October 2017
Line 55:		Line 55:
	an order recently and who exist in the CUSTOMERS table. In other words, only customers		an order recently and who exist in the CUSTOMERS table. In other words, only customers
	who are retrieved in both SELECTs should be in the results.		who are retrieved in both SELECTs should be in the results.
			''' RETURNS COMMON VALUES '''
			EXAMPLE;
			TABLE A CONTAINS APPLE, PEACH, BANANA
			TABLE B CONTAINS PEACH, BANANA, STRAWBERRY
			RESULTS WOULD BE PEACH, BANANA
			BECAUSE BOTH TABLES HAS PEACH AND BANANA IN COMMON

	SELECT customer#		SELECT customer#

2017-10-25T23:16:23Z

MINUS

← Older revision		Revision as of 23:16, 25 October 2017
Line 70:		Line 70:
	(customers in the CUSTOMERS table).		(customers in the CUSTOMERS table).

			SELECT customer#
			FROM customers
			MINUS
			SELECT customer#
			FROM orders;
	[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]		[[#Select Command\|Back To Top]]-[[Main_Page\| Home]] - [[Oracle_SQL\|Category]]

2017-10-24T01:30:14Z

MINUS

← Older revision		Revision as of 01:30, 24 October 2017
Line 64:		Line 64:
	==MINUS ==		==MINUS ==
	Subtracts the second query's result if they're also returned in the first query's results		Subtracts the second query's result if they're also returned in the first query's results
			a list of customer numbers for customers who
			are stored in the CUSTOMERS table but haven’t placed an order recently. To do this, you
			use the MINUS set operator to remove customer numbers returned by the second SELECT
			statement (customers in the ORDERS table) from the results of the first SELECT statement
			(customers in the CUSTOMERS table).


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

2017-10-24T01:26:18Z

INTERSECT

@@ Line 47: / Line 47: @@
 ==INTERSECT ==
 Returns only the rows included in the results of both queries
 ==MINUS ==

2017-10-24T01:18:43Z

UNION ALL

@@ Line 32: / Line 32: @@
    USING (isbn)
   WHERE category = 'CHILDREN';
 ==INTERSECT ==

2017-10-24T01:15:00Z

UNION ALL

← Older revision		Revision as of 01:15, 24 October 2017
Line 23:		Line 23:
	== UNION ALL ==		== UNION ALL ==
	Returns the results of both queries but includes duplicates		Returns the results of both queries but includes duplicates
			SELECT ba.authorid
			FROM books b JOIN bookauthor ba
			USING (isbn)
			WHERE category = 'FAMILY LIFE'
			UNION ALL
			SELECT ba.authorid
			FROM books b JOIN bookauthor ba
			USING (isbn)
			WHERE category = 'CHILDREN';

	==INTERSECT ==		==INTERSECT ==

2017-10-24T01:12:57Z

← Older revision		Revision as of 01:12, 24 October 2017
Line 11:		Line 11:
	== UNION==		== UNION==
	Returns the results of both queries and removes duplicates		Returns the results of both queries and removes duplicates
			SELECT ba.authorid
			FROM books b JOIN bookauthor ba
			USING (isbn)
			WHERE category = 'FAMILY LIFE'
			UNION
			SELECT ba.authorid
			FROM books b JOIN bookauthor ba
			USING (isbn)
			WHERE category = 'CHILDREN';

	== UNION ALL ==		== UNION ALL ==

2017-10-24T01:09:08Z

@@ Line 8: / Line 8: @@
 removes the second query’s results from the output if they are also found in the first query’s
 results. INTERSECT and MINUS set operations produce unduplicated results.
 [[#Select Command|Back To Top]]-[[Main_Page| Home]] - [[Oracle_SQL|Category]]