#sql syntax

(연재) SQLite3 강좌 – 테이블에서 조회하기 1

테이블에 저장된 데이터를 조회하기 위해서는 SELECT 구문을 사용한다. SELECT 구문운 SQL에서 가장 흔히 쓰이는 쿼리 구문인 동시에 가장 복잡한 구문이기도 하다. 테이블 조회와 관련하여 SQLite3는 표준 SQL에 정의된 거의 모든 기능을 제공한다. SQLite3의 SELECT 구문의 사용법에 대해서 살펴보자.

기본 컨셉

기본적으로 SELECT 는 DB 엔진으로부터 질의에 대한 답을 요청하는 명령이다. 가장 흔하게는 테이블 내의 레코드들을 조회하는 용도로 사용되지만, SELECT 명령의 본질은 DB가 수행할 수 있는 연산의 결과를 얻는 명령이다. 따라서 테이블 조회뿐만 아니라 다음과 같이 계산 결과를 얻도록 호출할 수도 있다.

SELECT 1 + 1;

위 쿼리는 단순히 2라는 결과를 얻게 된다.…

New Post has been published on http://www.softwaretestingclass.com/sql-union/

SQL UNION

In previous article we have learned about SQL JOINS. In this section of Software Testing Class we are concentrating on “SQL UNION”. In this post we are covering what is SQL UNION, examples of UNION, why use SQL UNION & difference between SQL JOIN and UNION.

  What is a SQL UNION?

The SQL UNION operator allows you to combine the result of two or more SQL SELECT queries.

The only requirement to work SQL UNION query is both the SQL SELECT queries must having the same number of columns in the result set & with same data type.

  SQL UNION Syntax:

SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;

  If we use UNION operator in the query then all unique records are returned in the result set. The duplicate records from the result set are displayed once.

  SQL UNION example:

Let take example, we have following two tables called Table1 and Table2:

    First we will see how SQL UNION operator is combining the results of the two select statements:

SELECT Column1, Column2 FROM Table1 UNION SELECT Column1, Column2 FROM Table2;

  Following is the result upon executing the above SQL UNION query, the only unique records are returned from both tables.

  Note: If we not specify any keyword with UNION operator then by default it take DISTINCT keyword i.e. “UNION DISTINCT”

  Now we will see how SQL UNION ALL operator is combining the results of the two select statements:

SELECT Column1, Column2 FROM Table1 UNION ALL SELECT Column1, Column2 FROM Table2;

  Following is the result set upon executing the above SQL UNION ALL query, the result set includes all records including duplicate records from both tables, so the duplicate rows are repeated in the result set.

    UNION operator is by default returns the unique records. If you want to get the all rows including duplicate records then you can use “ALL” keyword with UNION.

  Difference between SQL JOIN and UNION:

In the JOINS, the columns may be different in the tables but in the UNION the number of column must be same.

In case of UNION result set put rows one after each other i.e. result set puts vertically. But in case of JOIN result set put column one after each other i.e. result set puts horizontally. UNION makes a Cartesian product.

UNION is used for joining 2 result sets and JOIN is used for joining two or more tables, views, table-valued functions.

  Conclusion on SQL UNION Queries:

UNION operator is used to combine the result sets of two SELECT statements & combined result set includes all records from both tables.

The number of columns & column data types must be same for using the UNION operator.

The UNION DISTINCT keyword is returns the duplicate records from the result set. In the UNION operator if no any keyword is specified then it acts as DISTINCT keyword.

UNION ALL simply joins 2 result sets including all the rows,does not remove the duplicate records from the result set.

  Over to you on SQL UNION’s: Have you worked on Database testing? If yes, I would like all of you to please join this discussion and add more valuable points to it. If not please ask questions in comment below, also don’t forgot to subscribe here to get software testing articles in your inbox. Click here to subscribe with your email address or enter you email id below.

Enter your email address:

Check email in your inbox for confirmation to get latest updates Software Testing for free.