You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
The returned column name has the case that was used in the SELECT statement.
Table or Column Names
Oracle
Be no longer than 30 characters.
Begin with an alphabetical character.
Contain only alphabetical characters, numbers, or one of the following special characters: #, $, _
Data Types
String
CHAR(n): A fixed length string.
Blank-Padded Comparison Semantics: If the two values have different lengths, then Oracle first adds blanks to the end of the shorter one so their lengths are equal. Oracle then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks.
VARCHAR(n): A variable length string.
Nonpadded Comparison Semantics: Oracle compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, then the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal.
VARCHAR2(n)
There is no difference between VAHRCHAR and VARCHAR2 in Oracle. However, it is advised not to use VARCHAR for storing data as it is reserved for future use for storing some other type of variable. Hence, always use VARCHAR2 in place of VARCHAR.
Numeric
BOOL: Zero is considered as false, nonzero values are considered as true.
INT
FLOAT(n)
Date and Time
A later date is considered greater than an earlier one.
DATE: A date. Format: YYYY-MM-DD.
TIME: A time. Format: hh:mm:ss.
DATETIME: A date and time combination. Format: YYYY-MM-DD hh:mm:ss.
Execution Order
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT, TOP or FETCH
Error Messages
Column <<Column>> is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.
Operators
Arithmetic Operators(*, / -> +, -)
Any calculation performed on the NULL value returns NULL.
When you create a foreign key constraint, Oracle default to ON DELETE RESTRICT to ensure that a parent rows cannot be deleted while a child row still exists. However, you can also implement ON DELETE CASCADE to delete all child rows when a parent row is deleted.
A database role is a collection of any number of permissions/privileges that can be assigned to one or more users. A database role is also given a name for that collection of privileges.
The majority of today’s RDBMS’s come with predefined roles that can be assigned to any user. But, a database user can also create his/her own role if he or she has the CREATE ROLE privilege.
CREATE INDEX ON
CREATE INDEX <<Index_name>>ON<<Table>>(<<Column1>>, <<Column2>>, ...);
COMMIT is used to permanently save the changes done in the transaction in tables/databases. The database cannot regain its previous state after the execution of it.
COMMIT 되지 않은 데이터는 나 자신은 볼 수 있지만 다른 사용자는 볼 수 없으며 변경할 수도 없습니다.
SAVEPOINT
SAVEPOINT <<Savepoint Name>>
Use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back.
If you give two savepoints the same name, the earlier savepoint is erased.
ROLLBACK
ROLLBACK is used to undo the transactions that have not been saved in database. The command is only be used to undo changes since the last COMMIT.
Rolling back to a savepoint erases any savepoints marked after that savepoint. However, the savepoint to which you rollback is not erased.
The INSERT INTO SELECT FROM statement copies data from <<Source Table>> and inserts it into <<Target Table>>.
The INSERT INTO SELECT FROM statement requires that the data types in <<Source Table>> and <<Tartget Table>> match.
The existing records in the <<Target Table>> are unaffected.
INSERT ALL INTO SELECT FROM
When using an unconditional INSERT ALL statement, each row produced by the driving query results in a new row in each of the tables listed in the INTO clauses.
INSERT ALL
INTO pivot_dest(id, day, val) VALUES(id, 'mon', mon_val)
INTO pivot_dest(id, day, val) VALUES(id, 'tue', tue_val)
INTO pivot_dest(id, day, val) VALUES(id, 'wed', wed_val)
INTO pivot_dest(id, day, val) VALUES(id, 'thu', thu_val)
INTO pivot_dest(id, day, val) VALUES(id, 'fri', fri_val)
SELECT*FROM pivot_source;
INSERT FIRST WHEN THEN INTO ELSE INTO SELECT FROM
INSERT FIRST
WHEN id <=3 THEN INTO dest_tab1(id, description) VALUES(id, description)
WHEN id <=5 THEN INTO dest_tab2(id, description) VALUES(id, description)
ELSE INTO dest_tab3(id, description) VALUES(id, description)
SELECT id, description
FROM source_tab;
Using INSERT FIRST makes the multitable insert work like a CASE expression, so the conditions are tested until the first match is found, and no further conditions are tested.
SELECT
SELECT TOP FROM (MS SQL Server), SELECT FROM LIMIT (MySQL), SELECT FROM FETCH FIRST ROWS ONLY (Oracle)
SELECT TOP 1 months*salary, COUNT(employee_id)
FROM employee
GROUP BY months*salary
ORDER BY months*salary DESC;
SELECT*FROM Customers
FETCH FIRST 3 ROWS ONLY;
SELECT FROM ORDER BY
ORDER BY가 정렬을 하는 시점은 모든 실행이 끝난 후 데이터를 출력하기 바로 직전입니다.
ORDER BY 다음에 여러 개의 컬럼이 오면 앞에서부터 순서대로 정렬 적용합니다.
ORDER BY <<Number>>
<Number>> stands for the column based on the number of columns defined in the SELECT clause.
Oracle: NULL values are larger than any non-NULL values.
MS SQL Server: NULL values are smaller than any non-NULL values.
SELECT FROM ORDER BY NULLS FIRST | NULLS LAST
SELECT TOP PERCENT FROM (MS SQL Server), SELECT FROM FETCH FIRST PERCENT ROWS ONLY (Oracle)
SELECT ROWNUM FROM, SELECT FROM WHERE ROWNUM
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
The MERGE statement selects data from one or more source tables and updates or inserts it into a target table. The MERGE statement allows you to specify a condition to determine whether to update data from or insert data into the target table.
Because the MERGE is a deterministic statement, you cannot update the same row of the target table multiple times in the same MERGE statement.
MERGE INTO USING ON WHEN MATCHED THEN UPDATE SET WHERE [DELETE WHERE] WHEN NOT MATCHED THEN INSERT () VALUES() WHERE
MERGE INTO <<Target Table>>
USING <<Source Table>>ON<<Search Condition>>
WHEN MATCHED THEN
UPDATESET<<Column1>>=<<Value1>>, <<Column2>>=<<Value2>>, ...
WHERE<<Update Condition>>
[DELETEWHERE<<Delete Condition>>]
WHEN NOT MATCHED THEN
INSERT (<<Column1>>, <<Column2>>, ...)
VALUE(<<Value1>>, <<Value2>>, ...)
WHERE<<Insert Condition>>;
<<Search Condition>>
For each row in the target table, Oracle evaluates the <<Search Condition>>
If the result is true, then Oracle updates the row with the corresponding data from the source table.
In case the result is false for any rows, then Oracle inserts the corresponding row from the source table into the target table.
<<Delete Condition>>
You can add an optional DELETE WHERE clause to the MATCHED clause to clean up after a merge operation. The DELETE clause deletes only the rows in the target table that match both ON and DELETE WHERE clauses.
Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted. If you add a WHERE clause to the update in the matched clause, we can think of this as additional match criteria for the delete, as only rows that are touched by the update are available for the DELETE clause to remove. Depending on which table the DELETE WHERE references, it can target the rows prior or post update.
Hierarchical Queries
START WITH CONNECT BY PRIOR ORDER SIBLINGS BY
START WITH specifies the root row(s) of the hierarchy. START WITH 다음에 조건을 명시할 수도 있다.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. It always joins a table to itself, not to another table.
PRIOR should occur exactly once in each CONNECT BY expression. PRIOR can occur on either the left-hand side or the right-hand side of the expression, but not on both.
If the query contains a WHERE clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of the WHERE clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition, 0 otherwise.
CONNECT_BY_ROOT last_name AS boss
The CONNECT_BY_ROOT pseudocolumn returns the root.
SELECT last_name, employee_id, manager_id, LEVEL
FROM employees
START WITH employee_id =100
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;
If you want to order rows of siblings of the same parent, then use the ORDER SIBLINGS BY clause.
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle",
LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path"FROM employees
WHERE level <=3AND department_id =80
START WITH last_name ='King'
CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <=4;
The NOCYCLE parameter in the CONNECT BY condition causes Oracle to return the rows in spite of the loop.
The CONNECT_BY_ISCYCLE pseudocolumn shows you which rows contain the cycle.
SYS_CONNECT_BY_PATH(<<Column>>, <<Character>>)
The SYS_CONNECT_BY_PATH function returns the path of a <<Column>> value from root to node, with column values separated by <<Character>> for each row returned by CONNECT BY condition.
A null value indicates a lack of a value, which is not the same thing as a value of zero. For example, consider the question "How many books does Adam own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns). In a database table, the column reporting this answer would start out with no value (marked by NULL), and it would not be updated with the value "zero" until we have ascertained that Adam owns no books.
SQL null is a state, not a value.
NULL means that the value is unknown.
NVL() (Oracle)
NVL(<<Column to Test>>, <<Value if NULL>>)
Return <<Column to Test>> if not NULL otherwise return <<Value if NULL>> if NULL.
NVL2() (Oracle)
NVL2(<<Column to Test>>, <<Value if not NULL>>, <<Value if NULL>>)
Return <<Value if not NULL>> if <> is not NULL otherwise return <<Value if NULL>> if <<Column to Test>> is NULL.
COALESCE() (Oracle, MySQL)
COALESCE(<<Expression1>>, <<Expression2>>, ...)
Return the first non-NULL expression in the list. If all expressions evaluate to null, return NULL
NULLIF() (Oracle, MS SQL Server)
NULLIF(<<Expression1>>, <<Expression2>>)
Return NULL if <<Expression1>> and <<Expression2>> are equal and return <<Expression1>> if <<Expression1>> and <<Expression2>> are not equal.
IFNULL() (MySQL)
SELECT animal_type, IFNULL(name, "No name"), sex_upon_intake
FROM animal_ins
ORDER BY animal_id
It is not possible to test for NULL values with comparison operators(=, !=, <, <=, >, >=).
NULL IS NULL is TRUE and NULL IS NOT NULL is FALSE.
Because NULL represents a lack of data, a NULL cannot be equal or unequal to any value or to another NULL. However, Oracle considers two NULLs to be equal when evaluating a DECODE() function.(So NULL IN(NULL, ...) is FALSE)
Logical Functions
CASE WHEN THEN ELSE END
Search for conditions sequentially.
SELECT CASE WHEN (a >= b + c OR b >= c + a OR c >= a + b) THEN "Not A Triangle" WHEN (a = b AND b = c) THEN "Equilateral" WHEN (a = b OR b = c OR c = a) THEN "Isosceles" ELSE "Scalene" END
FROM triangles;
DECODE()
DECODE(<<Expression>>, <<Value to Be Compared1>>, <<Value If Equal to1>>, <<Value to Be Compared2>>, <<Value If Equal to2>>, ..., <<Default>>)
<<Default>>: (Optional) It is used to specify the default value to be returned if no matches are found.
ANY()
Return TRUE if any of the subquery values meet the condition.
SELECT name
FROM usertbl
WHERE height > ANY(
SELECT height
FROM usertbl
WHERE addr ='서울');
ALL()
Return TRUE if all of the subquery values meet the condition.
Used with SELECT, WHERE and HAVING statements.
IN(), NOT IN()
SELECT name, addr
FROM usertbl
WHERE addr IN("서울", "경기", "충청");
Numeric Functions
CEIL()(= CEILING()), FLOOR()
Returns the smallest(largest) integer value that is bigger(smaller) than or equal to a number.
ROUND()
ROUND(<<Target Number>>, [<<Decimal Place>>])
If no <> is defined (Oracle) or equal to zero (MS SQL Server), then <> is rounded to zero places.
If the <> specified is negative, then <> is rounded off to the left of the decimal point.
If the <> specified is positive, then <> is rounded up to <> decimal place(s).
ABS()
FORMAT() (MS SQL Server)
SELECT FORMAT(123456.123456, 4);
출력할 소수점 이하 자릿수 지정.
BETWEEN AND
SELECT name, height
FROMsqlDB.usertblWHERE height BETWEEN 180AND183;
String Functions
REPLACE()
SELECT CAST(CEILING(AVG(CAST(salary AS FLOAT)) -AVG(CAST(REPLACE(CAST(salary AS FLOAT), "0", "") AS FLOAT))) ASINT)
FROM employees;
LEFT(), RIGHT()
SELECT DISTINCT city
FROM station
WHERE RIGHT(city, 1) IN ("a", "e", "i", "o", "u");
<<String>>: The first position in the <<String>> is always 1, the last -1.
<<Length>>: If this parameter is omitted, the SUBSTR function will return the entire <<String>>.
LOWER(), UPPER(), INITCAP()
UPPER('eabc')
LPAD(), RPAD()
The LPAD() function left-pads a string with another string, to a certain length.
LPAD(<<Original String>>, <<Target Length>>, [<<String to Pad>>])
<<Target Length>>: The length of the string after it has been left-padded. Note that if the <<Target Length>> is less than the length of the <<Original String>>, then LPAD() function will shorten down the <<Original String>> to the <<Target Length>> without doing any padding.
<<String to Pad>> is equal to ' ' if omitted.
LTRIM(), RTRIM()
TRIM()
SELECT TRIM([[LEADING | TRAILING | BOTH] FROM]) FROM
TRIM(' tech ')
TRIM(''FROM' tech ')
TRIM(LEADING '0'FROM'000123')
TRIM(TRAILING '1'FROM'Tech1')
TRIM(BOTH '1'FROM'123Tech111')
CONCAT() (All)
SELECT CONCAT(CASE hdc_mbr.mbr_mst.mbr_sex WHEN '0' THEN '남자' WHEN '`' THEN '여자' END, '/', hdc_mbr.mbr_mst.mbr_birth)
The CONCAT_WS() function adds <<Expression1>>, <<Expression2>>, ... together with a <<Separator>>.
SELECT FROM WHERE LIKE
SELECT DISTINCT city
FROM station
WHERE city LIKE"%a"OR city LIke"%e"OR city LIKE"%i"OR city LIKE"%o"OR city LIKE"%u";
A wildcard character is used to substitute one or more characters in a string.
%: Represents zero, one, or multiple characters.
_: Represents one, single character.
-: Represents any single character within the specified range
SELECT FROM WHERE LIKE ESCAPE
SELECT*FROM tbl
WHERE name LIKE'%@_%` ESCAPE '@';
SELECT TO_CHAR() FROM
Used to convert a number or date to a string.
TO_CHAR(mbrmst.mbr_leaving_expected_date, "YYYY/MM/DD") AS mbr_leaving_expected_dt
Date Functions
DATE_FORMAT() (MySQL)
HOUR()
SELECT HOUR(datetime) AS hour, COUNT(*)
FROM animal_outs
GROUP BY hour
HAVING hour BETWEEN 9AND19ORDER BY hour;
HOUR()는 일반조건이므로 (COUNT()와 달리) HAIVNG과 함께 쓸 수 없다.
## `SYSDATE` (Oracle), `CURDATE()` (MySQL), `GETDATE()` (MS SQL Server)
## `EXTRACT([YEAR | MONTH | DAY | HOUR | MINUTE | SECOND] FROM)`
- Returns a numeric value according to the parameter.
```sql
EXTRACT(MONTH FROM order_date)
Window functions are aggregates based on a set of rows, similar to an aggregate function like a GROUP BY, but in this case this aggregation of rows moves or slides across a number of rows so we have a sort of sliding window, thus the name window functions.
Window operations do not collapse groups of query rows to a single output row. Instead, they produce a result for each row.
GROUP BY와 함께 사용할 수 없습니다.
OVER()
An empty OVER clause treats the entire set of query rows as a single partition. The window function thus produces a global sum, but does so for each row.
OVER([PARTITION BY])
OVER(PARTITION BY) clause partitions rows by a column, producing a sum per partition. The function produces this sum for each partition row.
OVER([PARTITION BY] [ORDER BY])
An ORDER BY clause indicates how to sort rows in each partition. If ORDER BY is omitted, partition rows are unordered.
An ORDER BY in a window definition applies within individual partitions. To sort the result set as a whole, include an ORDER BY at the query top level.
OVER([PARTITION BY] [ORDER BY] [ROWS BETWEEN AND]), OVER([PARTITION BY] [ORDER BY] [RANGE BETWEEN AND])
The RANGE and the ROW clauses have the same purpose: to specify the starting and ending points within the partition, with the goal of limiting rows. However, each clause does it differently. The ROW clause does it by specifying a fixed number of rows that precede or follow the current row. The RANGE clause, on the other hand, limits the rows logically; it specifies the range of values in relation to the value of the current row.
The default window frame without the ORDER BY is the whole partition. But when you use the ORDER BY, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(= UNBOUNDED PRECEDING).
BETWEEN <<Start Point>> AND <<End Point>>
<<Start Point>>: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW
<<End Point>>: UNBOUNDED FOLLOWING, m FOLLOWING, CURRENT ROW
<<Start Point>>
In this case, <<End Point>> is same as CURRENT ROW(<<End Point>> is omitted)
SELECT month, SUM(tot_sales) AS monthly_sales,
AVG(SUM(tot_sales)) OVER(ORDER BY month RANGE BETWEEN 1 PRECEDING AND1 FOLLOWING) AS rolling_avg
FROM orders
Aggregate Window Functions
MIN(), MAX()
AVG(), SUM()
NULL values are ignored.
COUNT()
COUNT(*), COUNT(1), COUNT(2), ...: Return the number of rows in the table.
COUNT(<<Column>>): Return the number of non-NULL values in the column.
COUNT(DISTINCT <<Column>>): Return the number of distinct non-NULL values in the column.
If there is a tie between N previous records for the value in the ORDER BY column, the RANK() function skips the next N-1 positions before incrementing the counter.(e.g., 1, 2, 2, 4, 4, 4, 7)
SELECT emp_no, emp_nm, sal,
RANK() OVER(ORDER BY salary DESC) ranking
FROM employee;
DENSE_RANK()
DENSE_RANK() function does not skip any ranks if there is a tie between the ranks of the preceding records.(e.g., 1, 2, 2, 3, 3, 3, 4)
ROW_NUMBER()
Give a unique sequential number for each row in the specified data. It gives the rank one for the first row and then increments the value by one for each row. We get different ranks for the row having similar values as well.(e.g., 1, 2, 3, 4, 5, 6, 7)
ROW_NUMBER()을 사용하면 Inline view를 사용하지 않고도 어떤 값의 순서대로 순번을 매길 수 있습니다.
NTILE() is a window function that allows you to break the result set into a specified number of approximately equal groups, or buckets. It assigns each group a bucket number starting from one. For each row in a group, the NTILE() function assigns a bucket number representing the group to which the row belongs.
The ORDER BY clause specifies the order of rows in each partition to which the NTILE() is applied.
Notice that if the number of rows is not divisible by buckets, the NTILE() function results in groups of two sizes with the difference by one. The larger groups always come before the smaller group in the order specified by the ORDER BY clause.
Value Window Functions
LAG(), LEAD()
The LAG function is used to access data from a previous row.
The LEAD function is used to return data from rows further down the result set.
RESPECT NULLS | IGNORE NULLS: It is an optional parameter which is used to specify whether to include or ignore the NULL values in the calculation. The default value is RESPECT NULLS.
SELECT DISTINCT FIRST_VALUE(marks)
OVER(ORDER BY marks DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS`Highest`FROM students;
Group Functions
SELECT FROM GROUP BY
GROUP BY treats NULL as valid values.
All NULL values are grouped into one value or bucket.
SELECT FROM GROUP BY ROLLUP()
GROUP BY ROLLUP(<<Column1>>, <<Column2>>, ...) is same as GROUP BY GROUPING SETS(<<Column1>>, (<<Column1>>, <<Column2>>), ..., ())
계층 구조로 계층 간 정렬이 가능합니다.
SELECT FROM GROUP BY CUBE()
Generate subtotals for all combinations of the dimensions specified.
GROUP BY CUBE(<<Column1>>, <<Column2>>, ...) is same as GROUP BY GROUPING SETS(<<Column1>>, <<Column2>>, ..., (<<Column1>>, <<Column2>>, ...), ())
SELECT FROM GROUP BY GROUPING SETS()
GROUP BY GROUPING SETS((<<Column1>>, <<Column2>>, ...))
GROUP BY <<Column1>>, <<Column2>>, ...과 동일합니다.
GROUP BY GROUPING SETS(())
전체에 대한 집계.
인자가 여러 개인 경우 위 3가지 경우의 각 결과를 UNION ALL한 것과 같음.
Set Operators
UNION, UNION ALL
SELECT FROM UNION(UNION ALL) SELECT FROM
The UNION operator eliminates duplicate selected rows. The sort is implicit in order to remove duplicates since.
INTERSECT, INTERSECT ALL
SELECT FROM INTERSECT(INTERSECT ALL) SELECT FROM
The INTERSECT operator returns only those rows returned by both queries.
The INTERSECT operator removes duplicate rows from the final result set.
The INTERSECT ALL operator does not remove duplicate rows from the final result set, but if a row appears X times in the first query and Y times in the second, it will appear min(X, Y) times in the result set.
MINUS (Oracle), EXCEPT, EXCEPT ALL (MS SQL Server)
SELECT FROM MINUS SELECT FROM, SELECT FROM EXCEPT(EXCEPT ALL) SELECT FROM
Return all rows in the first SELECT statement that are not returned by the second SELECT statement.
The MINUS(EXCEPT) operator removes duplicate rows from the final result set.
The EXCEPT ALL operator does not remove duplicates, but if a row appears X times in the first query and Y times in the second, it will appear max(X - Y, 0) times in the result set.
PIVOT, UNPIVOT
SELECT 반정보, 과목, 점수
FROM dbo.성적 UNPIVOT (점수 FOR 과목 IN(국어, 수학, 영어)) AS UNPVT
PIVOT transforms rows to columns.
UNPIVOT transforms columns to rows.
Join
INNER JOIN(= JOIN), LEFT OUTER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN
FULL OUTER JOIN is same as LEFT OUTER JOIN UNION ALL RIGHT OUTER JOIN.
CROSS JOIN
In Mathematics, given two sets A and B, the Cartesian product of AxB is the set of all ordered pair (a, b), which a belongs to A and b belongs to B.
Join key가 없을 때 발생합니다.
The CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN.This kind of result is called as Cartesian product.
If WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
A NATURAL JOIN is a join operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.
<<Table>>.<<Column>>과 같은 형태로 사용할 수 없습니다.
NATURAL INNER JOIN(= NATURAL JOIN), NATURAL LEFT OUTER JOIN, NATURAL RIGHT OUTER JOIN
SELECT*FROM countries NATURAL INNER JOIN cities;
SELECT*FROM countries INNER JOIN cities
USING(country, country_iso_code)
The above two statements are the same.
Join with + Operator
FROM A LEFT OUTER JOIN B ON A.<<Column1>>= B.<<Column2>>
FROM A, B
WHERE A.<<Column1>>= B.<<Column2>>(+)
The above two statements are the same.
FROM A RIGHT OUTER JOIN B ON A.<<Column1>>= B.<<Column2>>
Equi join performs a join against equality or matching column(s) values of the associated tables. An equal sign(=) is used as comparison operator in the where clause to refer equality.
Salesforce Object Query Language에서는 Equi join만 사용 가능합니다.
Single-Row Subquery: A single-row subquery is used when the outer query's results are based on a single, unknown value. Although this query type is formally called "single-row," the name implies that the query returns multiple columns-but only one row of results. However, a single-row subquery can return only one row of results consisting of only one column to the outer query.
=, !=, <, <=, >, >=
SELECT first_name, salary, department_id
FROM employees
WHERE salary = (SELECTMIN(salary)
FROM employees);
Multiple-Row Subquery: Multiple-row subqueries are nested queries that can return more than one row of results to the parent query
IN(), ALL(), ANY(), EXISTS()
A subquery can be placed in SELECT clause(Scala Subquery), FROM clause(Inline View), WHERE clause(Subquery), HAVING clause.
Inline View
Inline view is a subquery in the FROM clause of a SELECT statement.
SELECT hacker_id, name
FROM (
SELECTsu.hacker_id, ha.name,
CASE WHEN su.score=di.score THEN 1 ELSE 0 END AS is_full_score
FROM submissions AS su LEFT OUTER JOIN challenges AS ch ONsu.challenge_id=ch.challenge_idLEFT OUTER JOIN difficulty AS di ONch.difficulty_level=di.difficulty_levelLEFT OUTER JOIN hackers AS ha ONsu.hacker_id=ha.hacker_id) AS A
GROUP BY hacker_id, name
HAVINGSUM(is_full_score) >1ORDER BYSUM(is_full_score) DESC, hacker_id ASC;