Subqueries can produce single scalar values. In this (Multiple domain web hosting)
Subqueries can produce single scalar values. In this query, the subquery passes the AUTHOR_ID value for the filtered author, back to the query on the PUBLICATION table it passes a single AUTHOR_ID value (a single value is a scalar value): SELECT AUTHOR_ID, TITLE FROM PUBLICATION WHERE AUTHOR_ID = (SELECT AUTHOR_ID FROM AUTHOR WHERE NAME = James Blish ); AUTHOR_ID TITLE ———- ————————– 2 Cities in Flight The DISTINCT clause is used to return only the unique records in a set of returned records. Subqueries can also produce and be verified as multiple fields (this query returns no records): SELECT * FROM COAUTHOR WHERE (COAUTHOR_ID, PUBLICATION_ID) IN (SELECT A.AUTHOR_ID, P.PUBLICATION_ID FROM AUTHOR A JOIN PUBLICATION P ON (P.AUTHOR_ID = A.AUTHOR_ID)); The ON clause in join syntax allows specification of two fields from different tables to join on. The ON clause is used when join fields in the two joined tables have different names, or in this case, when the complexity of the query, and use of aliases, forces explicit join field specification. Composite Queries Set merge operators can be used to combine two separate queries into a merged composite query. Both queries must have the same data types for each field, all in the same sequence. The term set merge implies a merge or sticking together of two separate sets of data. In the case of the following query, all records from two different tables are merged into a single set of records: SELECT AUTHOR_ID AS ID, NAME FROM AUTHOR UNION SELECT PUBLISHER_ID AS ID, NAME FROM PUBLISHER; ID NAME ———- ——————————– 1 Orson Scott Card 1 Overlook Press 2 Ballantine Books Traditionally, the IN set membership operator is regarded as more efficient when testing against a list of literal values. The EXISTS set membership operator is regarded a being better than IN when checking against a subquery, in particular a correlated subquery. A correlated subquery creates a semi-join between the calling query and the subquery, by passing a key value from calling to subquery, allowing a join between calling query and subquery. This may not be true for all relational databases. A semi-join is called a semi-join because it effectively joins two tables but does not necessarily return any field values to the calling query, for return to the user, by the calling query. 143 Reading and Writing Data with SQL
Looking for affordable and reliable webhost to host and run your business application? Then look no more and go to servlet web hosting services.