Compare two columns together
mysql>
mysql> CREATE TABLE EmployeeS(
-> EmployeeNO INTEGER NOT NULL,
-> NAME CHAR(15) NOT NULL,
-> INITIALS CHAR(3) NOT NULL,
-> BIRTH_DATE DATE ,
-> SEX CHAR(1) NOT NULL,
-> JOINED SMALLINT NOT NULL,
-> STREET VARCHAR(30) NOT NULL,
-> HOUSENO CHAR(4) ,
-> POSTCODE CHAR(6) ,
-> TOWN VARCHAR(30) NOT NULL,
-> PHONENO CHAR(13) ,
-> LEAGUENO CHAR(4) ,
-> PRIMARY KEY (EmployeeNO) );
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (2, 'Jack', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (6, 'Link', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'First Way','39', '9758VB', 'Stratford', '070-347689', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (8, 'Mary', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long DRay','804', '8457DK', 'Eltham', '079-234857', '2513');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'First Way','16', '4377CB', 'Stratford', '070-473458', '6409');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Main Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (100, 'Link', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (104, 'Jane', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO EmployeeS VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql>
mysql> SELECT EmployeeNO
-> FROM EmployeeS
-> WHERE (NAME, INITIALS) = (TOWN, STREET);
Empty set (0.00 sec)
mysql>
mysql> drop table Employees;
Query OK, 0 rows affected (0.00 sec)
mysql>
Related examples in the same category
| 1. | Important Column Attributes and Options | | |
| 2. | Drop a column | | |
| 3. | Chage column name | | |
| 4. | Change column definition | | |
| 5. | Change column data length | | |
| 6. | Change column data type | | |
| 7. | change column type and sequence in table | | |
| 8. | Modify a column | | |
| 9. | Adding a Column | | |
| 10. | Changing a Column Definition | | |
| 11. | Renaming a Table | | |
| 12. | Dropping a Column | | |
| 13. | Syntax for Adding a Column | | |
| 14. | Returns detailed information about all columns of the table tablename | | |
| 15. | Exchanging Rows and Columns | | |
| 16. | To copy only some of the columns, name the ones you want in the SELECT part of the statement. | | |
| 17. | To create columns in a different order than that in which they appear in the source table | | |
| 18. | Get information only for the co_2 column, you can't use this query: | | |
| 19. | The -e option specifies the query to execute, and -N tells MySQL not to write the row of column names that normally precedes query output. | | |
| 20. | Copying Only Selected Columns from a Table | | |
| 21. | Specifying Which Columns to Display | | |
| 22. | Giving Names to Output Columns | | |
| 23. | Aliases can be applied to any result set column, not just those that come from tables | | |
| 24. | Combining Columns to Construct Composite Values | | |
| 25. | Put the comparison expression in the output column list, perhaps including the values that you're comparing | | |
| 26. | Refer to the additional output column by position | | |
| 27. | Take everything but the rightmost two columns | | |
| 28. | Dropping, Adding, or Repositioning a Column | | |
| 29. | To indicate a column at a specific position within the table, either use FIRST to make it the first column, or | | |
| 30. | Changing a Column Definition or Name | | |
| 31. | After the CHANGE keyword, you name the column you want to change, then specify the new definition, which inclu | | |
| 32. | Changing a Column's Default Value | | |
| 33. | Information only about a single column, use a LIKE clause that matches the column name | | |
| 34. | -d option specifies a field delimiter of : and the -f option indicates that you want to cut column one and all | | |
| 35. | Add a sequence column named id to the table | | |
| 36. | Give each table a short alias and refer to table columns using the aliases: | | |
| 37. | To exclude them, provide a column output list that names specifically only those columns | | |
| 38. | To produce additional columns showing the total and average values of the books for each author in the author | | |
| 39. | CREATE TABLE statement provides an example of a TEXT column named DescriptionDoc | | |
| 40. | Create the Catalog table and includes two NOT NULL columns | | |
| 41. | Reference a column | | |
| 42. | Add and drop columns | | |
| 43. | Giving Columns a New Heading with AS | | |
| 44. | Alias column with spaces | | |
| 45. | Insert data without column names | | |
| 46. | Column definition with modifiers | | |
| 47. | Sort by column name | | |
| 48. | Sort more than one columns | | |
| 49. | Get the descriptive data of the columns belonging to the PLAYERS table | | |
| 50. | A table contains only one column. | | |
| 51. | A column definition may include options to define the column data more precisely. | | |
| 52. | More complex tables have multiple columns | | |
| 53. | Adding and Dropping Columns | | |
| 54. | Modifying Existing Columns | | |
| 55. | Change the LastName column from CHAR(30) to CHAR(40) without renaming the column | | |
| 56. | Column Types | | |
| 57. | NULL Values and Column Definitions | | |
| 58. | Use single quote with char type column | | |
| 59. | Column alias with underscore | | |
| 60. | Using Case clause to show meaningful value for column | | |
| 61. | Select all columns from a table inside another database | | |
| 62. | Concatenate columns | | |
| 63. | Using qulified column name | | |
| 64. | Compare two columns and yield two values | | |
| 65. | Column sequence in insert statemenet | | |
| 66. | Change column type and settings during table copying | | |
| 67. | Enlarge column during table copying | | |
| 68. | Set column to use default value | | |
| 69. | Get default value from a column | | |
| 70. | Table column with comments | | |
| 71. | Check table column settings | | |
| 72. | Unique modifier for column definition | | |
| 73. | Indicate unique after column definition | | |
| 74. | Check column value | | |
| 75. | Set type column | | |
| 76. | To disallow NULL values in any of the columns, add NOT NULL to the definition of each one. | | |