SQL interview a subject of transnational corporations
left outer join
right outer join
full outer join
A difference between the four
is a cross join is a Cartesian product of the number of rows in table another table multiplied by the number of rows
a left join b: to return to a full line - "b in the line to meet and b in the line does not meet (with null instead of)
a right join b: ibid change what is ab
to return to full join two lines in the table left join + right join
inner join table to connect only to return two out of the match
a table
1, 'x'
2, 'y'
3, 'z'
b Table
1, 'a'
4, 'b'
5, 'c'
connect with inner join:
select a.id, a.name, b.id, b.name from a, b
where a.id = b.id
1, 'x', 1, 'a'
left outer join left connected
select a.id, a.name, b.id, b.name from a
left outer join b on a.id = b.id
1, 'x', 1, 'a'
2, 'y', null, null
3, 'z', null, null
right outer join right connections
select a.id, a.name, b.id, b.name from a
right outer join b on a.id = b.id
1, 'x', 1, 'a'
null, null, 4, 'b'
null, null, 5, 'c'
outer join full outer join
select a.id, a.name, b.id, b.name from a
full outer join b on a.id = b.id
1, 'x', 1, 'a'
2, 'y', null, null
3, 'z', null, null
null, null, 4, 'b'
null, null, 5, 'c'
Related Posts of SQL interview a subject of transnational corporations
-
1Z0-007 Study Notes Lesson 5 multi-table queries
1Z0-007 Introduction to Oracle9i SQL ® Lesson 5 multi-table queries SLIDE 1 goal After the completion of this lesson should be able to use equality and nonequality joins multi-table data query Using the OUTER JOIN query data Table query using SELF JO ...
-
sql
join table summary statement select count (*) from test, test1 where test.id = test1.id-- the intersection of select count (*) from test inner join test1 on test.id = test1.id-- the intersection of select count (*) from test cross join test1 - Cartes ...
-
ORACLE PL / SQL Management Command 1
ORACLE necessarily familiar with the management of these commands will not be unfamiliar, but for me this is new to ORACLE administration still need to do the next record in order to keep track. 1 Log in SQLPLUS sqlplus username / password @ database ...
-
SQL Connection to connect the left and right connections, fully connected, internal connections, cross-connect, self-connection
Now write about their role in this hypothesis the following table: A main table for polling, a voter information table ~ records for the voters to vote IP and the corresponding type, so the actual connection is the result of our joint query to which ...
-
MERGE
MERGE in Oracle 10g provided in the implementation of the data operation to remove the option line. You can WHEN MATCHED THEN UPDATE clause contained in DELETE clause. DELETE must have a WHERE clause conditions to remove the line match certain condit ...
-
Joins
Standard: ■ SQL: 2006 Syntax: SELECT table1.column, table2.column FROM table1 [NATURAL JOIN table2] | [JOIN table2 USING (column_name)] | [JOIN table2 ON (table1.column_name = table2.column_name)] | [LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.c ...
-
Block db_block_size the reasons for the operating system?
rn I would like to ask That all the information on db_block_size to be configured to block the entire operating system several times to improve the processing speed, But do not indicate the reason for that? The boss explains why ah? : em14: rn rn ivh ...
-
Cross-version to use Exp / Imp
Cross-version to use Exp / Imp rn rn Exp / Imp In many cases, can be used across versions, such as between version 7 and version 8 export import data, but it must choose the correct version, the rules as follows: rn · always use IMP's version of ...
-
Oracle to connect the left and right connections
In the Oracle PL-SQL, the left and right connecting link in the following ways to achieve View the following statement: SELECT emp_name, dept_name FORM Employee, Department WHERE Employee.emp_deptid (+) = Department.deptid this SQL text using the rig ...
-
Basic data types of Oracle storage format analysis (d) - ROWID type (a)
Oracle's ROWID is used to uniquely identify a record in the table, is this data stored in the database, the physical address. Oracle's ROWID is divided into two kinds: physical ROWID and logical ROWID. Index-organized tables using logical ROW ...
Leave a Reply