{"id":229,"date":"2018-07-13T05:39:56","date_gmt":"2018-07-13T05:39:56","guid":{"rendered":"http:\/\/itp9.epgpbooks.inflibnet.ac.in\/?post_type=chapter&#038;p=229"},"modified":"2018-08-10T11:08:12","modified_gmt":"2018-08-10T11:08:12","slug":"sql-ii-in-mysql","status":"publish","type":"chapter","link":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/chapter\/sql-ii-in-mysql\/","title":{"rendered":"SQL \u2013 II in MySQL"},"content":{"raw":"<div>\r\n\r\n&nbsp;\r\n\r\n<strong>Objectives<\/strong>\r\n<ul>\r\n \t<li><strong>How to create primary key and foreign key and other constraints<\/strong><\/li>\r\n \t<li><strong>Write Query using more than one table using join<\/strong><\/li>\r\n \t<li><strong>Use of Union operators<\/strong><\/li>\r\n<\/ul>\r\n<strong>\u00a0 \u00a0 Constraints:<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Data is important for any database. The data must be consistent in database table. To make data consistent, there is required to restrict inconsistent data input. Constraint is used to restrict inconsistent data.<\/p>\r\n&nbsp;\r\n<p style=\"text-align: justify\">Constraints are used to enforce the integrity of the data. To maintain integrity of the data by defining rules about the values that can be stored in the columns of the table.<\/p>\r\n&nbsp;\r\n\r\nConstraints can be defined at column level or table level.\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">MySQL support primary key, foreign key, unique and not null constraint. The primary key, foreign key and unique constraint can defined at table level and column level while not null is defined only at column level.<\/p>\r\n&nbsp;\r\n\r\n<strong>Primary key:<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Primary key is used to uniquely identify a record in a table. In case the primary key consists of set of attributes, it is known as composite primary key.<\/p>\r\n&nbsp;\r\n<p style=\"text-align: justify\">Primary key is used to main entity integrity for a table. Entity integrity ensures that the attribute must be unique within a table and must not null.<\/p>\r\n&nbsp;\r\n\r\nThe table has primary key defined is referred as master table or parent table.\r\n\r\n&nbsp;\r\n\r\nThere must be at most one primary key in any database table.\r\n\r\n&nbsp;\r\n\r\nThe syntax of column level primary key constraint is\r\n\r\n&nbsp;\r\n\r\n&lt;Column_name&gt;\u00a0 &lt; dataType&gt; primary key\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Following example, show how to create a table general_ledger_accounts with primary key Accno at column level.<\/p>\r\n&nbsp;\r\n\r\nCREATE TABLE general_ledger_accounts\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">\u00a0(<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">AccNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY,<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">AccDes\u00a0 VARCHAR(50)<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">);<\/span>\r\n\r\n<\/div>\r\n<div>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">In the above example, primary key defined at column level. To define primary key at table level, the general syntax is:<\/p>\r\n&nbsp;\r\n<p style=\"text-align: justify\">[constraint [constraintName]] primary key (columnName1 [, columnName2] \u2026)<\/p>\r\n&nbsp;\r\n\r\nThe table level constraint is defined\u00a0 after defining all column.\r\n\r\n&nbsp;\r\n\r\ncreate table accounts2\r\n\r\n(accno int,\r\n\r\naccdesc varchar(50),\r\n\r\nconstraint pk_accno primary key(accno)\r\n\r\n);\r\n\r\n&nbsp;\r\n\r\nIn case , a primary key is consist of more than one attributes (composite key) , it must be defined at table level.\r\n\r\n&nbsp;\r\n\r\n<strong>Foreign key:<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Foreign key is used to create relationship between tables. Foreign key constraint requires values in one table to match values in another table. Foreign key constraint is used to enforce referential integrity.<\/p>\r\n&nbsp;\r\n\r\nThe database is relational database because there is a relation can be set between tables of the database.\r\n\r\n&nbsp;\r\n\r\nThe relationship between tables is defined by using foreign key.\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Referential integrity ensures that the values of foreign key column must have matching primary key values in the related table.<\/p>\r\n&nbsp;\r\n<p style=\"text-align: justify\">Foreign key is used to provide referential integrity between tables. Referential integrity means an attribute can take only values available in parent table. For instance, considered we have an order table. Then in order table only those customer can be placed order which are available in customer table.<\/p>\r\n&nbsp;\r\n\r\nThe table in which foreign key is defined is known as child table or detail table.\r\n\r\n&nbsp;\r\n\r\nA table can have zero or more foreign keys.\r\n\r\n&nbsp;\r\n\r\nThe foreign key column must have the same data type as the primary key column related to it.\r\n\r\n&nbsp;\r\n\r\nReferential integrity can be violated in following cases.\r\n\r\n<\/div>\r\n<div>\r\n<ul>\r\n \t<li style=\"text-align: justify\">Delete a row from the primary key table while the foreign key table contains one or more rows with a matching primary key value.<\/li>\r\n \t<li style=\"text-align: justify\">Update the value of a primary key while the foreign key table contains one or more rows having a matching value of primary key.<\/li>\r\n \t<li style=\"text-align: justify\">Insert a row in the foreign key table but the foreign key value does not have a matching primary key value.<\/li>\r\n \t<li style=\"text-align: justify\">Update a row in the foreign key table but the new (updated) value does not have a matching primary key value.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: justify\">The on delete cascade statement is used to make referential integrity not violated when primary key value is deleted from primary key table. It deletes all the corresponding record from child table of matching primary key values.<\/p>\r\n&nbsp;\r\n<p style=\"text-align: justify\">The on update cascade statement is used to make referential integrity not violated when primary key value is updated from primary key table. It updates all the corresponding record foreign key values from child table of matching primary key values.<\/p>\r\n&nbsp;\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">MySQL only enforces referential integrity if the table is defined using InnoDB storage engine. In case, for a table the storage engine is not InnoDB, then foreign key can be coded but MySQL does not enforce the referential integrity.<\/p>\r\n&nbsp;\r\n\r\nTo define foreign key constraint at column-level, the general syntax is\r\n\r\nreferences\u00a0 &lt;tableName&gt;\u00a0 (columnName1 [, columnName2] \u2026)\r\n\r\n[on update &lt;reference option&gt; ]\r\n\r\n[on delete &lt;reference option&gt;]\r\n<ul>\r\n \t<li>Reference option can be any one value of the<\/li>\r\n \t<li>{Restrict | cascade | set null | No action}<\/li>\r\n \t<li>Restrict rejects the delete or update operation for the parent table when referential integrity is violated.<\/li>\r\n \t<li style=\"text-align: justify\">No action and restrict are the same as no action behave the same as restrict. It is also the same as omitting the on delete or on update clause.<\/li>\r\n \t<li style=\"text-align: justify\">Set null is used to set null value(s) in the child table for the parent table primary key value is\u00a0 updated\u00a0 or deleted.<\/li>\r\n<\/ul>\r\nFollowing example create a column level foreign key.\r\n\r\n&nbsp;\r\n\r\nCREATE TABLE vendors1\r\n\r\n(\r\n\r\n<\/div>\r\n&nbsp;\r\n<div>\r\n\r\n\u00a0 \u00a0 \u00a0Vid\u00a0 \u00a0INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY ,\r\n\r\n&nbsp;\r\n\r\nVname\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR(50),\r\n\r\n&nbsp;\r\n\r\nAccNo\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 references accounts2(accno)\r\n\r\n<\/div>\r\n<div>\r\n\r\n\u00a0 \u00a0 );\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\"><strong>Be remember that in MySQL though syntactically foreign key can be defined at column level, it does not enforce referential integrity. To enforce referential integrity it must be defined at table level.<\/strong><\/p>\r\n&nbsp;\r\n\r\nTo define foreign key constraint at table-level, the general syntax is [Constraint &lt;constraintName&gt; ]\r\n\r\n&nbsp;\r\n\r\nForeign key (columnName1 [, columnName2] \u2026)\r\n\r\n&nbsp;\r\n\r\nReferences &lt;tablename&gt;\u00a0 (columnName1 [, columnName2] \u2026)\r\n\r\n&nbsp;\r\n\r\n[on update {cascade| set null } ]\r\n\r\n&nbsp;\r\n\r\n[on delete {cascade | set null }]\r\n\r\n&nbsp;\r\n\r\nFollowing example shows table level foreign key.\r\n\r\n&nbsp;\r\n\r\nCREATE TABLE vendors2\r\n\r\n&nbsp;\r\n\r\n(\r\n\r\n&nbsp;\r\n\r\nVid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY,\r\n\r\n&nbsp;\r\n\r\nVname\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR(50),\r\n\r\n&nbsp;\r\n\r\nAcctNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT,\r\n\r\n&nbsp;\r\n\r\nconstraint fk_accno\r\n\r\nforeign key (Acctno)\r\n\r\nreferences accounts1(accno)\r\n\r\n&nbsp;\r\n\r\n);\r\n\r\n&nbsp;\r\n\r\n<strong>Unique<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Unique constraint allows only unique value of the column. It allows null value also.<span style=\"text-align: initial;font-size: 1em\">A table can have zero or more columns have unique constraint.<\/span><span style=\"text-align: initial;font-size: 1em\">Following example shows table level unique constraint.<\/span><\/p>\r\n\r\n<\/div>\r\n<div>\r\n\r\n&nbsp;\r\n\r\ncreate table vendors3\r\n\r\n(\r\n\r\nvid int primary key,\r\n\r\nvname varchar(50) unique,\r\n\r\naccno int\r\n\r\n)\r\n\r\n&nbsp;\r\n\r\nTo create constraint at table level.\r\n\r\n&nbsp;\r\n\r\ncreate table vendors4\r\n\r\n(\r\n\r\nvid int primary key,\r\n\r\nvname varchar(50),\r\n\r\naccno int,\r\n\r\nconstraint uvname unique(vname)\r\n\r\n) ;\r\n\r\n&nbsp;\r\n\r\n<strong>Not null:<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">As we know that null is a special value. Null is neither zero nor empty string. Not null constraint can only defined at column level only. A table can have many columns defined as not null. Not null constraint does not allow null value.<\/p>\r\n&nbsp;\r\n\r\ncreate table vendors5\r\n\r\n(\r\n\r\nvid int primary key,\r\n\r\nvname varchar(25) not null\r\n\r\n);\r\n\r\n&nbsp;\r\n\r\n<strong>Join:<\/strong>\r\n\r\n<\/div>\r\n<div>\r\n<p style=\"text-align: justify\"><\/p>\r\n<p style=\"text-align: justify\">To understand join, let us created following tables and insert values into it. For that you can also import a .sql file through phpmyadmin. The database is created with tables and values are inserted into it.<\/p>\r\n&nbsp;\r\n<p style=\"text-align: justify\">A join is used to retrieve data from multiple tables. A join is used to combine columns from two or more tables into a single result set. The result set is based on the join conditions specified by query writer.<\/p>\r\n&nbsp;\r\n\r\nJoin types are classified as I. inner join and II. outer join\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">Inner join only included those rows in the result set which are satisfied the join condition. The implicit syntax for inner join is shown below.<\/p>\r\n&nbsp;\r\n\r\nSelect\u00a0 &lt;column list&gt;\r\n\r\nFrom &lt;tableName1&gt; ,\u00a0 &lt;tableName2&gt;\u00a0 [,&lt;tableName3&gt; \u2026]\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">Where &lt;tableName1. columnName&gt;\u00a0 \u00a0&lt;operator&gt;\u00a0 \u00a0&lt;tableName2. columnName&gt;<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">[And &lt;tableName2. columnName&gt;\u00a0 \u00a0 \u00a0\u00a0&lt;operator&gt;\u00a0 \u00a0\u00a0&lt;tableName3. columnName&gt; ] \u2026<\/span>\r\n\r\n<\/div>\r\n&nbsp;\r\n\r\n<span style=\"text-align: justify;font-size: 1em\">The operator can be any relation operator like = , &gt;, &gt;=, &lt;,&lt;= etc. If the operator is = (equal to) then the join is known as equi-join.<\/span>\r\n<div>\r\n\r\n&nbsp;\r\n\r\nselect ino, vname from vendors , invoices where vendors.vid = invoices.vid order by ino;\r\n<ul>\r\n \t<li style=\"text-align: justify\">Tables are generally joined on the relationship between the primary key in one table and foreign key in the other table. However, join can be done on tables based on relationship not defined in the database. These are called ad hoc relationship.<\/li>\r\n \t<li style=\"text-align: justify\">In case , if the columns included in a join condition have the same name, then to differentiate them write tablename.columnname.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: justify\">select ino, vname, vendors.vid from vendors , invoices where vendors.vid = invoices.vid order by ino;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">Table alias is an alternative table name assigned in the from clause. Table alias is typically one or two character long. Table alias is used to make SQL statement easier to code and read. If a table\u00a0<span style=\"text-align: initial;font-size: 1em\">alias is assigned , then it must be used throughout the query. You cannot use original table name once alias is used.<\/span><\/li>\r\n<\/ul>\r\n<\/div>\r\n<div>\r\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 select ino, vname\u00a0 from vendors v, invoices i\u00a0 where v.vid = i.vid order by ino;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">An alias can be used for one table in a join without using an alias for another table. select ino, vname from vendors , invoices i where vendors.vid = i.vid<\/li>\r\n \t<li style=\"text-align: justify\">A join condition can include two or more conditions connected by AND or OR logical operators.<\/li>\r\n<\/ul>\r\n<p style=\"text-align: center\">select ino, lineitemamt, lineitemdes<\/p>\r\n<p style=\"text-align: center\">from invoices, invoice_line_items iline<\/p>\r\n<p style=\"text-align: center\">where invoices.iid = iline.iid<\/p>\r\n<p style=\"text-align: center\">and accno = 150;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">A self- join is a join that joins a table to itself. When you code self- join, you must use table aliases and qualify column with &lt;tablename&gt;.&lt;columnName&gt;<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: center\">select distinct v1.vname, v1.vcity, v1.vstate<\/p>\r\n<p style=\"text-align: center\">from vendors v1, vendors v2<\/p>\r\n<p style=\"text-align: center\">where v1.vcity = v2.vcity and v1.vstate = v2.vstate and v1.vname &lt;&gt; v2.vname;<\/p>\r\n\r\n<ul>\r\n \t<li style=\"text-align: justify\">You can write query using explicit syntax of join. It is used after SQL-92 standards. SQL-92 standards are defined in 1992. Then SQL \u2013 1999 , SQL \u2013 2003 and SQL-2008 standard are defined. Current version of SQL standard is 2008. The syntax for join using SQL 2003\/SQL-2008 is known as explicit syntax. The explicit syntax for join is<\/li>\r\n<\/ul>\r\n&nbsp;\r\n<p style=\"text-align: justify\">Select\u00a0 &lt;column list&gt;<\/p>\r\n<p style=\"text-align: justify\">From &lt;tableName1&gt;\u00a0 [inner]\u00a0 join &lt;tableName2&gt;<\/p>\r\n<p style=\"text-align: justify\"><span style=\"font-size: 1em;text-align: initial\">on &lt;tableName1. columnName&gt;\u00a0 &lt;operator&gt;\u00a0\u00a0 &lt;tableName2. columnName&gt;<\/span><\/p>\r\n<p style=\"text-align: justify\"><span style=\"font-size: 1em\">[[inner]\u00a0 join\u00a0 &lt;tableName2. columnName&gt; &lt;operator&gt;\u00a0\u00a0 &lt;tableName3. columnName&gt; ] \u2026<\/span><\/p>\r\n\r\n<\/div>\r\n<div>\r\n\r\n\u00a0 \u00a0select ino, vname\r\n\r\nfrom vendors join invoices\r\n\r\non vendors.vid = invoices.vid\r\n\r\norder by ino;\r\n\r\n&nbsp;\r\n\r\nYou can join more than two tables also.\r\n\r\n&nbsp;\r\n\r\nselect vname, ino, idate, lineitemamt, accdes, itotal,paymenttotal,credittotal\r\n\r\nfrom vendors v, invoices i, invoice_line_items il, general_ledger_accounts gl\r\n\r\nwhere v.vid = i.vid\r\n\r\nand i.iid = il.iid\r\n\r\nand il.accno = gl.accno\r\n\r\nand (itotal - paymenttotal - credittotal) &gt;= 0\r\n\r\norder by vname, lineitemamt desc;\r\n\r\n&nbsp;\r\n\r\n<strong>Outer Join<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">An outer join is used to retrieve all rows that satisfied the condition plus unmatched rows from the left or right table.<\/li>\r\n \t<li style=\"text-align: justify\">The left outer join retrieves all rows that satisfied the condition plus unmatched rows from the left hand side table.<\/li>\r\n \t<li style=\"text-align: justify\">The right outer join retrieves all rows that satisfied the condition plus unmatched rows from the right hand side table.<\/li>\r\n \t<li style=\"text-align: justify\">The unmatched rows value are null in the result set.<\/li>\r\n \t<li style=\"text-align: justify\">The syntax for outer join is<\/li>\r\n<\/ul>\r\nSelect\u00a0 &lt;column list&gt;\r\n\r\nFrom &lt;tableName1&gt; {Left | Right} [outer] join &lt;tableName2&gt;\r\n\r\nOn join_condition_1\r\n\r\n[{Left | Right} [outer] join\u00a0 &lt;tableName3&gt;\r\n\r\nOn join_condition_2 ]\u2026\r\n\r\n&nbsp;\r\n\r\n<strong>Left Outer Join<\/strong>\r\n\r\n&nbsp;\r\n\r\nselect\u00a0 vname, ino,itotal\r\n\r\n<span style=\"font-size: 1em;text-align: initial\">from vendors left join invoices<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">on vendors.vid = invoices.vid<\/span>\r\n\r\n<span style=\"text-align: initial;font-size: 1em\">order by vname;<\/span>\r\n\r\n<\/div>\r\n<div>\r\n\r\n&nbsp;\r\n\r\n<strong>Right Outer Join<\/strong>\r\n\r\n&nbsp;\r\n\r\nselect vname,ino,itotal\r\n\r\nfrom vendors right join invoices\r\n\r\non vendors.vid = invoices.vid\r\n\r\norder by vname;\r\n<ul>\r\n \t<li>Outer join can be used to join more than two tables.<\/li>\r\n \t<li>Inner join and outer join can combined within a single select statement.<\/li>\r\n<\/ul>\r\n&nbsp;\r\n\r\nselect\u00a0 ino,itotal\r\n\r\nfrom invoices i\u00a0 join invoice_line_items\u00a0 il\r\n\r\non i.iid = il.iid\r\n\r\nleft join general_ledger_accounts lg\r\n\r\non il.accno = lg.accno\r\n\r\norder by ino;\r\n\r\n&nbsp;\r\n\r\n<strong>Cross Join<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">Cross join joins each row from the first table with each row from the second table. The result set of cross join is also known as Cartesian product.<\/li>\r\n<\/ul>\r\nselect ino, vname\u00a0 from vendors cross join invoices;\r\n\r\n&nbsp;\r\n\r\n<strong>Union<\/strong>\r\n<ul>\r\n \t<li style=\"text-align: justify\">A union is used to combine the result set of two or more select statements into a single result set.<\/li>\r\n \t<li style=\"text-align: justify\">For union, each result must have the same number of columns.<\/li>\r\n \t<li>Each corresponding columns of the each result must be compatible data type to make union.<\/li>\r\n \t<li style=\"text-align: justify\">By default, a union eliminates duplicates rows. To include duplicate rows use All keyword after union keyword immediately.<\/li>\r\n \t<li style=\"text-align: justify\">The final result set includes column name from first select clause only. Column alias assigned by the other select clause have no effect on the final result set.<\/li>\r\n \t<li style=\"text-align: justify\">To sort the rows in the final result set, code an order by clause after the last select stamen. The order by clause must refer to the column name specified in the first clause.Syntax for union: &lt;Select-statement-1&gt; Union [All] &lt;Select-statement-2&gt; [ union [all] &lt;Select-statement-3&gt; ] \u2026 [order by &lt;order-by-list&gt; ]<\/li>\r\n<\/ul>\r\n<\/div>\r\nFollowing code shows an example of union\r\n\r\n&nbsp;\r\n\r\nselect 'Active' as source, ino,idate, itotal\r\n\r\nfrom invoices\r\n\r\nwhere idate &gt;= '2011-04-10' union\r\n\r\n&nbsp;\r\n\r\nselect 'paid' as source, ino,idate,itotal\r\n\r\nfrom invoices\r\n\r\nwhere idate &lt;= '2011-04-10' order by itotal desc;\r\n<ul>\r\n \t<li>Full outer join includes left outer join plus right outer join.<\/li>\r\n \t<li style=\"text-align: justify\">MySQL does not provide keyword full outer join to implement full outer join. But Left Outer join union right outer join provides full outer joy.<\/li>\r\n<\/ul>\r\n<strong>\u00a0 \u00a0 References:<\/strong>\r\n\r\n&nbsp;\r\n<p style=\"text-align: justify\">1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,<\/p>\r\n<p style=\"text-align: justify\">2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress<\/p>\r\n<p style=\"text-align: justify\">3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,<\/p>\r\n<p style=\"text-align: justify\">4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O'Reilly Media<\/p>\r\n<p style=\"text-align: justify\">5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox<\/p>\r\n<p style=\"text-align: justify\">6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible<\/p>\r\n<p style=\"text-align: justify\">7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach<\/p>\r\n<p style=\"text-align: justify\">8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML\/Javascript\/DHTML\/PHP , BPB Publications<\/p>\r\n<p style=\"text-align: justify\">9. Joel Murach, \u201cMurach\u2019s MySQL\u201d, Shroff\/Murach<\/p>\r\n<p style=\"text-align: justify\">10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams<\/p>\r\n<p style=\"text-align: justify\">11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education<\/p>\r\n<p style=\"text-align: justify\">12. http:\/\/www.php.net\/<\/p>\r\n<p style=\"text-align: justify\">13. http:\/\/www.w3schools.com\/<\/p>\r\n<p style=\"text-align: justify\">14. http:\/\/www.tutorialspoint.com\/<\/p>","rendered":"<div>\n<p>&nbsp;<\/p>\n<p><strong>Objectives<\/strong><\/p>\n<ul>\n<li><strong>How to create primary key and foreign key and other constraints<\/strong><\/li>\n<li><strong>Write Query using more than one table using join<\/strong><\/li>\n<li><strong>Use of Union operators<\/strong><\/li>\n<\/ul>\n<p><strong>\u00a0 \u00a0 Constraints:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Data is important for any database. The data must be consistent in database table. To make data consistent, there is required to restrict inconsistent data input. Constraint is used to restrict inconsistent data.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Constraints are used to enforce the integrity of the data. To maintain integrity of the data by defining rules about the values that can be stored in the columns of the table.<\/p>\n<p>&nbsp;<\/p>\n<p>Constraints can be defined at column level or table level.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">MySQL support primary key, foreign key, unique and not null constraint. The primary key, foreign key and unique constraint can defined at table level and column level while not null is defined only at column level.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Primary key:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Primary key is used to uniquely identify a record in a table. In case the primary key consists of set of attributes, it is known as composite primary key.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Primary key is used to main entity integrity for a table. Entity integrity ensures that the attribute must be unique within a table and must not null.<\/p>\n<p>&nbsp;<\/p>\n<p>The table has primary key defined is referred as master table or parent table.<\/p>\n<p>&nbsp;<\/p>\n<p>There must be at most one primary key in any database table.<\/p>\n<p>&nbsp;<\/p>\n<p>The syntax of column level primary key constraint is<\/p>\n<p>&nbsp;<\/p>\n<p>&lt;Column_name&gt;\u00a0 &lt; dataType&gt; primary key<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Following example, show how to create a table general_ledger_accounts with primary key Accno at column level.<\/p>\n<p>&nbsp;<\/p>\n<p>CREATE TABLE general_ledger_accounts<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">\u00a0(<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">AccNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY,<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">AccDes\u00a0 VARCHAR(50)<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">);<\/span><\/p>\n<\/div>\n<div>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">In the above example, primary key defined at column level. To define primary key at table level, the general syntax is:<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">[constraint [constraintName]] primary key (columnName1 [, columnName2] \u2026)<\/p>\n<p>&nbsp;<\/p>\n<p>The table level constraint is defined\u00a0 after defining all column.<\/p>\n<p>&nbsp;<\/p>\n<p>create table accounts2<\/p>\n<p>(accno int,<\/p>\n<p>accdesc varchar(50),<\/p>\n<p>constraint pk_accno primary key(accno)<\/p>\n<p>);<\/p>\n<p>&nbsp;<\/p>\n<p>In case , a primary key is consist of more than one attributes (composite key) , it must be defined at table level.<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Foreign key:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Foreign key is used to create relationship between tables. Foreign key constraint requires values in one table to match values in another table. Foreign key constraint is used to enforce referential integrity.<\/p>\n<p>&nbsp;<\/p>\n<p>The database is relational database because there is a relation can be set between tables of the database.<\/p>\n<p>&nbsp;<\/p>\n<p>The relationship between tables is defined by using foreign key.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Referential integrity ensures that the values of foreign key column must have matching primary key values in the related table.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Foreign key is used to provide referential integrity between tables. Referential integrity means an attribute can take only values available in parent table. For instance, considered we have an order table. Then in order table only those customer can be placed order which are available in customer table.<\/p>\n<p>&nbsp;<\/p>\n<p>The table in which foreign key is defined is known as child table or detail table.<\/p>\n<p>&nbsp;<\/p>\n<p>A table can have zero or more foreign keys.<\/p>\n<p>&nbsp;<\/p>\n<p>The foreign key column must have the same data type as the primary key column related to it.<\/p>\n<p>&nbsp;<\/p>\n<p>Referential integrity can be violated in following cases.<\/p>\n<\/div>\n<div>\n<ul>\n<li style=\"text-align: justify\">Delete a row from the primary key table while the foreign key table contains one or more rows with a matching primary key value.<\/li>\n<li style=\"text-align: justify\">Update the value of a primary key while the foreign key table contains one or more rows having a matching value of primary key.<\/li>\n<li style=\"text-align: justify\">Insert a row in the foreign key table but the foreign key value does not have a matching primary key value.<\/li>\n<li style=\"text-align: justify\">Update a row in the foreign key table but the new (updated) value does not have a matching primary key value.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">The on delete cascade statement is used to make referential integrity not violated when primary key value is deleted from primary key table. It deletes all the corresponding record from child table of matching primary key values.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">The on update cascade statement is used to make referential integrity not violated when primary key value is updated from primary key table. It updates all the corresponding record foreign key values from child table of matching primary key values.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">MySQL only enforces referential integrity if the table is defined using InnoDB storage engine. In case, for a table the storage engine is not InnoDB, then foreign key can be coded but MySQL does not enforce the referential integrity.<\/p>\n<p>&nbsp;<\/p>\n<p>To define foreign key constraint at column-level, the general syntax is<\/p>\n<p>references\u00a0 &lt;tableName&gt;\u00a0 (columnName1 [, columnName2] \u2026)<\/p>\n<p>[on update &lt;reference option&gt; ]<\/p>\n<p>[on delete &lt;reference option&gt;]<\/p>\n<ul>\n<li>Reference option can be any one value of the<\/li>\n<li>{Restrict | cascade | set null | No action}<\/li>\n<li>Restrict rejects the delete or update operation for the parent table when referential integrity is violated.<\/li>\n<li style=\"text-align: justify\">No action and restrict are the same as no action behave the same as restrict. It is also the same as omitting the on delete or on update clause.<\/li>\n<li style=\"text-align: justify\">Set null is used to set null value(s) in the child table for the parent table primary key value is\u00a0 updated\u00a0 or deleted.<\/li>\n<\/ul>\n<p>Following example create a column level foreign key.<\/p>\n<p>&nbsp;<\/p>\n<p>CREATE TABLE vendors1<\/p>\n<p>(<\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<div>\n<p>\u00a0 \u00a0 \u00a0Vid\u00a0 \u00a0INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY ,<\/p>\n<p>&nbsp;<\/p>\n<p>Vname\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0VARCHAR(50),<\/p>\n<p>&nbsp;<\/p>\n<p>AccNo\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u00a0INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 references accounts2(accno)<\/p>\n<\/div>\n<div>\n<p>\u00a0 \u00a0 );<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\"><strong>Be remember that in MySQL though syntactically foreign key can be defined at column level, it does not enforce referential integrity. To enforce referential integrity it must be defined at table level.<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>To define foreign key constraint at table-level, the general syntax is [Constraint &lt;constraintName&gt; ]<\/p>\n<p>&nbsp;<\/p>\n<p>Foreign key (columnName1 [, columnName2] \u2026)<\/p>\n<p>&nbsp;<\/p>\n<p>References &lt;tablename&gt;\u00a0 (columnName1 [, columnName2] \u2026)<\/p>\n<p>&nbsp;<\/p>\n<p>[on update {cascade| set null } ]<\/p>\n<p>&nbsp;<\/p>\n<p>[on delete {cascade | set null }]<\/p>\n<p>&nbsp;<\/p>\n<p>Following example shows table level foreign key.<\/p>\n<p>&nbsp;<\/p>\n<p>CREATE TABLE vendors2<\/p>\n<p>&nbsp;<\/p>\n<p>(<\/p>\n<p>&nbsp;<\/p>\n<p>Vid\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY,<\/p>\n<p>&nbsp;<\/p>\n<p>Vname\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 VARCHAR(50),<\/p>\n<p>&nbsp;<\/p>\n<p>AcctNo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INT,<\/p>\n<p>&nbsp;<\/p>\n<p>constraint fk_accno<\/p>\n<p>foreign key (Acctno)<\/p>\n<p>references accounts1(accno)<\/p>\n<p>&nbsp;<\/p>\n<p>);<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Unique<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Unique constraint allows only unique value of the column. It allows null value also.<span style=\"text-align: initial;font-size: 1em\">A table can have zero or more columns have unique constraint.<\/span><span style=\"text-align: initial;font-size: 1em\">Following example shows table level unique constraint.<\/span><\/p>\n<\/div>\n<div>\n<p>&nbsp;<\/p>\n<p>create table vendors3<\/p>\n<p>(<\/p>\n<p>vid int primary key,<\/p>\n<p>vname varchar(50) unique,<\/p>\n<p>accno int<\/p>\n<p>)<\/p>\n<p>&nbsp;<\/p>\n<p>To create constraint at table level.<\/p>\n<p>&nbsp;<\/p>\n<p>create table vendors4<\/p>\n<p>(<\/p>\n<p>vid int primary key,<\/p>\n<p>vname varchar(50),<\/p>\n<p>accno int,<\/p>\n<p>constraint uvname unique(vname)<\/p>\n<p>) ;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Not null:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">As we know that null is a special value. Null is neither zero nor empty string. Not null constraint can only defined at column level only. A table can have many columns defined as not null. Not null constraint does not allow null value.<\/p>\n<p>&nbsp;<\/p>\n<p>create table vendors5<\/p>\n<p>(<\/p>\n<p>vid int primary key,<\/p>\n<p>vname varchar(25) not null<\/p>\n<p>);<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Join:<\/strong><\/p>\n<\/div>\n<div>\n<p style=\"text-align: justify\">\n<p style=\"text-align: justify\">To understand join, let us created following tables and insert values into it. For that you can also import a .sql file through phpmyadmin. The database is created with tables and values are inserted into it.<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">A join is used to retrieve data from multiple tables. A join is used to combine columns from two or more tables into a single result set. The result set is based on the join conditions specified by query writer.<\/p>\n<p>&nbsp;<\/p>\n<p>Join types are classified as I. inner join and II. outer join<\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Inner join only included those rows in the result set which are satisfied the join condition. The implicit syntax for inner join is shown below.<\/p>\n<p>&nbsp;<\/p>\n<p>Select\u00a0 &lt;column list&gt;<\/p>\n<p>From &lt;tableName1&gt; ,\u00a0 &lt;tableName2&gt;\u00a0 [,&lt;tableName3&gt; \u2026]<\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">Where &lt;tableName1. columnName&gt;\u00a0 \u00a0&lt;operator&gt;\u00a0 \u00a0&lt;tableName2. columnName&gt;<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">[And &lt;tableName2. columnName&gt;\u00a0 \u00a0 \u00a0\u00a0&lt;operator&gt;\u00a0 \u00a0\u00a0&lt;tableName3. columnName&gt; ] \u2026<\/span><\/p>\n<\/div>\n<p>&nbsp;<\/p>\n<p><span style=\"text-align: justify;font-size: 1em\">The operator can be any relation operator like = , &gt;, &gt;=, &lt;,&lt;= etc. If the operator is = (equal to) then the join is known as equi-join.<\/span><\/p>\n<div>\n<p>&nbsp;<\/p>\n<p>select ino, vname from vendors , invoices where vendors.vid = invoices.vid order by ino;<\/p>\n<ul>\n<li style=\"text-align: justify\">Tables are generally joined on the relationship between the primary key in one table and foreign key in the other table. However, join can be done on tables based on relationship not defined in the database. These are called ad hoc relationship.<\/li>\n<li style=\"text-align: justify\">In case , if the columns included in a join condition have the same name, then to differentiate them write tablename.columnname.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">select ino, vname, vendors.vid from vendors , invoices where vendors.vid = invoices.vid order by ino;<\/p>\n<ul>\n<li style=\"text-align: justify\">Table alias is an alternative table name assigned in the from clause. Table alias is typically one or two character long. Table alias is used to make SQL statement easier to code and read. If a table\u00a0<span style=\"text-align: initial;font-size: 1em\">alias is assigned , then it must be used throughout the query. You cannot use original table name once alias is used.<\/span><\/li>\n<\/ul>\n<\/div>\n<div>\n<p style=\"text-align: justify\">\u00a0 \u00a0 \u00a0 select ino, vname\u00a0 from vendors v, invoices i\u00a0 where v.vid = i.vid order by ino;<\/p>\n<ul>\n<li style=\"text-align: justify\">An alias can be used for one table in a join without using an alias for another table. select ino, vname from vendors , invoices i where vendors.vid = i.vid<\/li>\n<li style=\"text-align: justify\">A join condition can include two or more conditions connected by AND or OR logical operators.<\/li>\n<\/ul>\n<p style=\"text-align: center\">select ino, lineitemamt, lineitemdes<\/p>\n<p style=\"text-align: center\">from invoices, invoice_line_items iline<\/p>\n<p style=\"text-align: center\">where invoices.iid = iline.iid<\/p>\n<p style=\"text-align: center\">and accno = 150;<\/p>\n<ul>\n<li style=\"text-align: justify\">A self- join is a join that joins a table to itself. When you code self- join, you must use table aliases and qualify column with &lt;tablename&gt;.&lt;columnName&gt;<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: center\">select distinct v1.vname, v1.vcity, v1.vstate<\/p>\n<p style=\"text-align: center\">from vendors v1, vendors v2<\/p>\n<p style=\"text-align: center\">where v1.vcity = v2.vcity and v1.vstate = v2.vstate and v1.vname &lt;&gt; v2.vname;<\/p>\n<ul>\n<li style=\"text-align: justify\">You can write query using explicit syntax of join. It is used after SQL-92 standards. SQL-92 standards are defined in 1992. Then SQL \u2013 1999 , SQL \u2013 2003 and SQL-2008 standard are defined. Current version of SQL standard is 2008. The syntax for join using SQL 2003\/SQL-2008 is known as explicit syntax. The explicit syntax for join is<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">Select\u00a0 &lt;column list&gt;<\/p>\n<p style=\"text-align: justify\">From &lt;tableName1&gt;\u00a0 [inner]\u00a0 join &lt;tableName2&gt;<\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 1em;text-align: initial\">on &lt;tableName1. columnName&gt;\u00a0 &lt;operator&gt;\u00a0\u00a0 &lt;tableName2. columnName&gt;<\/span><\/p>\n<p style=\"text-align: justify\"><span style=\"font-size: 1em\">[[inner]\u00a0 join\u00a0 &lt;tableName2. columnName&gt; &lt;operator&gt;\u00a0\u00a0 &lt;tableName3. columnName&gt; ] \u2026<\/span><\/p>\n<\/div>\n<div>\n<p>\u00a0 \u00a0select ino, vname<\/p>\n<p>from vendors join invoices<\/p>\n<p>on vendors.vid = invoices.vid<\/p>\n<p>order by ino;<\/p>\n<p>&nbsp;<\/p>\n<p>You can join more than two tables also.<\/p>\n<p>&nbsp;<\/p>\n<p>select vname, ino, idate, lineitemamt, accdes, itotal,paymenttotal,credittotal<\/p>\n<p>from vendors v, invoices i, invoice_line_items il, general_ledger_accounts gl<\/p>\n<p>where v.vid = i.vid<\/p>\n<p>and i.iid = il.iid<\/p>\n<p>and il.accno = gl.accno<\/p>\n<p>and (itotal &#8211; paymenttotal &#8211; credittotal) &gt;= 0<\/p>\n<p>order by vname, lineitemamt desc;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Outer Join<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">An outer join is used to retrieve all rows that satisfied the condition plus unmatched rows from the left or right table.<\/li>\n<li style=\"text-align: justify\">The left outer join retrieves all rows that satisfied the condition plus unmatched rows from the left hand side table.<\/li>\n<li style=\"text-align: justify\">The right outer join retrieves all rows that satisfied the condition plus unmatched rows from the right hand side table.<\/li>\n<li style=\"text-align: justify\">The unmatched rows value are null in the result set.<\/li>\n<li style=\"text-align: justify\">The syntax for outer join is<\/li>\n<\/ul>\n<p>Select\u00a0 &lt;column list&gt;<\/p>\n<p>From &lt;tableName1&gt; {Left | Right} [outer] join &lt;tableName2&gt;<\/p>\n<p>On join_condition_1<\/p>\n<p>[{Left | Right} [outer] join\u00a0 &lt;tableName3&gt;<\/p>\n<p>On join_condition_2 ]\u2026<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Left Outer Join<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>select\u00a0 vname, ino,itotal<\/p>\n<p><span style=\"font-size: 1em;text-align: initial\">from vendors left join invoices<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">on vendors.vid = invoices.vid<\/span><\/p>\n<p><span style=\"text-align: initial;font-size: 1em\">order by vname;<\/span><\/p>\n<\/div>\n<div>\n<p>&nbsp;<\/p>\n<p><strong>Right Outer Join<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p>select vname,ino,itotal<\/p>\n<p>from vendors right join invoices<\/p>\n<p>on vendors.vid = invoices.vid<\/p>\n<p>order by vname;<\/p>\n<ul>\n<li>Outer join can be used to join more than two tables.<\/li>\n<li>Inner join and outer join can combined within a single select statement.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>select\u00a0 ino,itotal<\/p>\n<p>from invoices i\u00a0 join invoice_line_items\u00a0 il<\/p>\n<p>on i.iid = il.iid<\/p>\n<p>left join general_ledger_accounts lg<\/p>\n<p>on il.accno = lg.accno<\/p>\n<p>order by ino;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Cross Join<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">Cross join joins each row from the first table with each row from the second table. The result set of cross join is also known as Cartesian product.<\/li>\n<\/ul>\n<p>select ino, vname\u00a0 from vendors cross join invoices;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>Union<\/strong><\/p>\n<ul>\n<li style=\"text-align: justify\">A union is used to combine the result set of two or more select statements into a single result set.<\/li>\n<li style=\"text-align: justify\">For union, each result must have the same number of columns.<\/li>\n<li>Each corresponding columns of the each result must be compatible data type to make union.<\/li>\n<li style=\"text-align: justify\">By default, a union eliminates duplicates rows. To include duplicate rows use All keyword after union keyword immediately.<\/li>\n<li style=\"text-align: justify\">The final result set includes column name from first select clause only. Column alias assigned by the other select clause have no effect on the final result set.<\/li>\n<li style=\"text-align: justify\">To sort the rows in the final result set, code an order by clause after the last select stamen. The order by clause must refer to the column name specified in the first clause.Syntax for union: &lt;Select-statement-1&gt; Union [All] &lt;Select-statement-2&gt; [ union [all] &lt;Select-statement-3&gt; ] \u2026 [order by &lt;order-by-list&gt; ]<\/li>\n<\/ul>\n<\/div>\n<p>Following code shows an example of union<\/p>\n<p>&nbsp;<\/p>\n<p>select &#8216;Active&#8217; as source, ino,idate, itotal<\/p>\n<p>from invoices<\/p>\n<p>where idate &gt;= &#8216;2011-04-10&#8217; union<\/p>\n<p>&nbsp;<\/p>\n<p>select &#8216;paid&#8217; as source, ino,idate,itotal<\/p>\n<p>from invoices<\/p>\n<p>where idate &lt;= &#8216;2011-04-10&#8217; order by itotal desc;<\/p>\n<ul>\n<li>Full outer join includes left outer join plus right outer join.<\/li>\n<li style=\"text-align: justify\">MySQL does not provide keyword full outer join to implement full outer join. But Left Outer join union right outer join provides full outer joy.<\/li>\n<\/ul>\n<p><strong>\u00a0 \u00a0 References:<\/strong><\/p>\n<p>&nbsp;<\/p>\n<p style=\"text-align: justify\">1. Luke Welling, Laura Thomson: PHP and MySQL Web Development, Pearson,<\/p>\n<p style=\"text-align: justify\">2. W. Jason Gilmore: Beginning PHP and MySQL 5 From Novice to Professional, Apress<\/p>\n<p style=\"text-align: justify\">3. Elizabeth Naramore, Jason Gerner, Yann Le Scouarnec, Jeremy Stolz, Michael K. Glass:Beginning PHP5, Apache, and MySQL Web Development, Wrox,<\/p>\n<p style=\"text-align: justify\">4. Robin Nixon: Learning PHP, MySQL, and JavaScript, O&#8217;Reilly Media<\/p>\n<p style=\"text-align: justify\">5. Ed Lecky-Thompson, Heow Eide-Goodman, Steven D. Nowicki, Alec Cove: Professional PHP,Wrox<\/p>\n<p style=\"text-align: justify\">6. Tim Converse, Joyce Park, Clark Morgan: PHP5 and MySQL Bible<\/p>\n<p style=\"text-align: justify\">7. Joel Murach, Ray Harris: Murach\u2019s PHP and MySQL, Shroff\/Murach<\/p>\n<p style=\"text-align: justify\">8. Ivan Bayross, Web Enabled Commercial Application Development Using HTML\/Javascript\/DHTML\/PHP , BPB Publications<\/p>\n<p style=\"text-align: justify\">9. Joel Murach, \u201cMurach\u2019s MySQL\u201d, Shroff\/Murach<\/p>\n<p style=\"text-align: justify\">10. Julie C. Meloni, Sams Teach Yourself PHP, MySQL and Apache All in One, Sams<\/p>\n<p style=\"text-align: justify\">11. Larry Ullman, PHP and MySQL for Dynamic Web Sites: Visual QuickPro Guide, Pearson Education<\/p>\n<p style=\"text-align: justify\">12. http:\/\/www.php.net\/<\/p>\n<p style=\"text-align: justify\">13. http:\/\/www.w3schools.com\/<\/p>\n<p style=\"text-align: justify\">14. http:\/\/www.tutorialspoint.com\/<\/p>\n","protected":false},"author":3,"menu_order":23,"template":"","meta":{"pb_show_title":"on","pb_short_title":"","pb_subtitle":"","pb_authors":["dr-hiren-joshi"],"pb_section_license":""},"chapter-type":[],"contributor":[59],"license":[],"class_list":["post-229","chapter","type-chapter","status-publish","hentry","contributor-dr-hiren-joshi"],"part":3,"_links":{"self":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/229","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters"}],"about":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/types\/chapter"}],"author":[{"embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/users\/3"}],"version-history":[{"count":4,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/229\/revisions"}],"predecessor-version":[{"id":420,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/229\/revisions\/420"}],"part":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/parts\/3"}],"metadata":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapters\/229\/metadata\/"}],"wp:attachment":[{"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/media?parent=229"}],"wp:term":[{"taxonomy":"chapter-type","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/pressbooks\/v2\/chapter-type?post=229"},{"taxonomy":"contributor","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/contributor?post=229"},{"taxonomy":"license","embeddable":true,"href":"https:\/\/ebooks.inflibnet.ac.in\/itp9\/wp-json\/wp\/v2\/license?post=229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}