Your cart is currently empty!
Author: alien
-
Khóa học miễn phí PostgreSQL – Joins nhận dự án làm có lương
PostgreSQL – JOINS
The PostgreSQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
Join Types in PostgreSQL are −
- The CROSS JOIN
- The INNER JOIN
- The LEFT OUTER JOIN
- The RIGHT OUTER JOIN
- The FULL OUTER JOIN
Before we proceed, let us consider two tables, COMPANY and DEPARTMENT. We already have seen INSERT statements to populate COMPANY table. So just let us assume the list of records available in COMPANY table −
id | name | age | address | salary | join_date ----+-------+-----+-----------+--------+----------- 1 | Paul | 32 | California| 20000 | 2001-07-13 3 | Teddy | 23 | Norway | 20000 | 4 | Mark | 25 | Rich-Mond | 65000 | 2007-12-13 5 | David | 27 | Texas | 85000 | 2007-12-13 2 | Allen | 25 | Texas | | 2007-12-13 8 | Paul | 24 | Houston | 20000 | 2005-07-13 9 | James | 44 | Norway | 5000 | 2005-07-13 10 | James | 45 | Texas | 5000 | 2005-07-13
Another table is DEPARTMENT, has the following definition −
CREATE TABLE DEPARTMENT( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT NOT NULL );
Here is the list of INSERT statements to populate DEPARTMENT table −
INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (1, ''IT Billing'', 1 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (2, ''Engineering'', 2 ); INSERT INTO DEPARTMENT (ID, DEPT, EMP_ID) VALUES (3, ''Finance'', 7 );
Finally, we have the following list of records available in DEPARTMENT table −
id | dept | emp_id ----+-------------+-------- 1 | IT Billing | 1 2 | Engineering | 2 3 | Finance | 7
The CROSS JOIN
A CROSS JOIN matches every row of the first table with every row of the second table. If the input tables have x and y columns, respectively, the resulting table will have x+y columns. Because CROSS JOINs have the potential to generate extremely large tables, care must be taken to use them only when appropriate.
The following is the syntax of CROSS JOIN −
SELECT ... FROM table1 CROSS JOIN table2 ...
Based on the above tables, we can write a CROSS JOIN as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY CROSS JOIN DEPARTMENT;
The above given query will produce the following result −
emp_id| name | dept ------|-------|-------------- 1 | Paul | IT Billing 1 | Teddy | IT Billing 1 | Mark | IT Billing 1 | David | IT Billing 1 | Allen | IT Billing 1 | Paul | IT Billing 1 | James | IT Billing 1 | James | IT Billing 2 | Paul | Engineering 2 | Teddy | Engineering 2 | Mark | Engineering 2 | David | Engineering 2 | Allen | Engineering 2 | Paul | Engineering 2 | James | Engineering 2 | James | Engineering 7 | Paul | Finance 7 | Teddy | Finance 7 | Mark | Finance 7 | David | Finance 7 | Allen | Finance 7 | Paul | Finance 7 | James | Finance 7 | James | FinanceThe INNER JOIN
A INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows, which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of table1 and table2 are combined into a result row.
An INNER JOIN is the most common type of join and is the default type of join. You can use INNER keyword optionally.
The following is the syntax of INNER JOIN −
SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_filed = table2.common_field;
Based on the above tables, we can write an INNER JOIN as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY INNER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;The above given query will produce the following result −
emp_id | name | dept --------+-------+------------ 1 | Paul | IT Billing 2 | Allen | EngineeringThe LEFT OUTER JOIN
The OUTER JOIN is an extension of the INNER JOIN. SQL standard defines three types of OUTER JOINs: LEFT, RIGHT, and FULL and PostgreSQL supports all of these.
In case of LEFT OUTER JOIN, an inner join is performed first. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. Thus, the joined table always has at least one row for each row in T1.
The following is the syntax of LEFT OUTER JOIN −
SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...
Based on the above tables, we can write an inner join as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY LEFT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above given query will produce the following result −
emp_id | name | dept --------+-------+------------ 1 | Paul | IT Billing 2 | Allen | Engineering | James | | David | | Paul | | Mark | | Teddy | | James |The RIGHT OUTER JOIN
First, an inner join is performed. Then, for each row in table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in columns of T1. This is the converse of a left join; the result table will always have a row for each row in T2.
The following is the syntax of RIGHT OUTER JOIN −
SELECT ... FROM table1 RIGHT OUTER JOIN table2 ON conditional_expression ...
Based on the above tables, we can write an inner join as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY RIGHT OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above given query will produce the following result −
emp_id | name | dept --------+-------+-------- 1 | Paul | IT Billing 2 | Allen | Engineering 7 | | FinanceThe FULL OUTER JOIN
First, an inner join is performed. Then, for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values in columns of T2. In addition, for each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added.
The following is the syntax of FULL OUTER JOIN −
SELECT ... FROM table1 FULL OUTER JOIN table2 ON conditional_expression ...
Based on the above tables, we can write an inner join as follows −
testdb=# SELECT EMP_ID, NAME, DEPT FROM COMPANY FULL OUTER JOIN DEPARTMENT ON COMPANY.ID = DEPARTMENT.EMP_ID;
The above given query will produce the following result −
emp_id | name | dept --------+-------+--------------- 1 | Paul | IT Billing 2 | Allen | Engineering 7 | | Finance | James | | David | | Paul | | Mark | | Teddy | | James |
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc
Khóa học miễn phí PostgreSQL – Constraints nhận dự án làm có lương
PostgreSQL – CONSTRAINTS
Constraints are the rules enforced on data columns on table. These are used to prevent invalid data from being entered into the database. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column whereas table level constraints are applied to the whole table. Defining a data type for a column is a constraint in itself. For example, a column of type DATE constrains the column to valid dates.
The following are commonly used constraints available in PostgreSQL.
-
NOT NULL Constraint − Ensures that a column cannot have NULL value.
-
UNIQUE Constraint − Ensures that all values in a column are different.
-
PRIMARY Key − Uniquely identifies each row/record in a database table.
-
FOREIGN Key − Constrains data based on columns in other tables.
-
CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions.
-
EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows are compared on the specified column(s) or expression(s) using the specified operator(s), not all of these comparisons will return TRUE.
NOT NULL Constraint
By default, a column can hold NULL values. If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column. A NOT NULL constraint is always written as a column constraint.
A NULL is not the same as no data; rather, it represents unknown data.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY1 and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULL values −
CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
UNIQUE Constraint
The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having identical age.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY3 and adds five columns. Here, AGE column is set to UNIQUE, so that you cannot have two records with same age −
CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );
PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table. There can be more UNIQUE columns, but only one primary key in a table. Primary keys are important when designing the database tables. Primary keys are unique ids.
We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a ”longstanding coding oversight”, primary keys can be NULL in SQLite. This is not the case with other databases
A primary key is a field in a table, which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).
Example
You already have seen various examples above where we have created COMAPNY4 table with ID as primary key −
CREATE TABLE COMPANY4( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
FOREIGN KEY Constraint
A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables. They are called foreign keys because the constraints are foreign; that is, outside the table. Foreign keys are sometimes called a referencing key.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns.
CREATE TABLE COMPANY6( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );
For example, the following PostgreSQL statement creates a new table called DEPARTMENT1, which adds three columns. The column EMP_ID is the foreign key and references the ID field of the table COMPANY6.
CREATE TABLE DEPARTMENT1( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT references COMPANY6(ID) );
CHECK Constraint
The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and is not entered into the table.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns. Here, we add a CHECK with SALARY column, so that you cannot have any SALARY as Zero.
CREATE TABLE COMPANY5( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );
EXCLUSION Constraint
Exclusion constraints ensure that if any two rows are compared on the specified columns or expressions using the specified operators, at least one of these operator comparisons will return false or null.
Example
For example, the following PostgreSQL statement creates a new table called COMPANY7 and adds five columns. Here, we add an EXCLUDE constraint −
CREATE TABLE COMPANY7( ID INT PRIMARY KEY NOT NULL, NAME TEXT, AGE INT , ADDRESS CHAR(50), SALARY REAL, EXCLUDE USING gist (NAME WITH =, AGE WITH ) );
Here, USING gist is the type of index to build and use for enforcement.
You need to execute the command CREATE EXTENSION btree_gist, once per database. This will install the btree_gist extension, which defines the exclusion constraints on plain scalar data types.
As we have enforced the age has to be same, let us see this by inserting records to the table −
INSERT INTO COMPANY7 VALUES(1, ''Paul'', 32, ''California'', 20000.00 ); INSERT INTO COMPANY7 VALUES(2, ''Paul'', 32, ''Texas'', 20000.00 ); INSERT INTO COMPANY7 VALUES(3, ''Paul'', 42, ''California'', 20000.00 );
For the first two INSERT statements, the records are added to the COMPANY7 table. For the third INSERT statement, the following error is displayed −
ERROR: conflicting key value violates exclusion constraint "company7_name_age_excl" DETAIL: Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).
Dropping Constraints
To remove a constraint you need to know its name. If the name is known, it is easy to drop. Else, you need to find out the system-generated name. The psql command d table name can be helpful here. The general syntax is −
ALTER TABLE table_name DROP CONSTRAINT some_name;
Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc