SQL Joins
SQL Joins
If you currently have the ‘departments_dup’ table set up, use DROP COLUMN to remove the ‘dept_manager’ column from the ‘departments_dup’ table.
Then, use CHANGE COLUMN to change the ‘dept_no’ and ‘dept_name’ columns to NULL.
(If you don’t currently have the ‘departments_dup’ table set up, create it. Let it contain two columns: dept_no and dept_name. Let the data type of dept_no be CHAR of 4, and the data type of dept_name be VARCHAR of 40. Both columns are allowed to have null values. Finally, insert the information contained in ‘departments’ into ‘departments_dup’.)
Then, insert a record whose department name is “Public Relations”.
Delete the record(s) related to department number two.
Insert two new records in the “departments_dup” table. Let their values in the “dept_no” column be “d010” and “d011”.
# if you currently have ‘departments_dup’ set up:
ALTER TABLE departments_dup
DROP COLUMN dept_manager;
ALTER TABLE departments_dup
CHANGE COLUMN dept_no dept_no CHAR(4) NULL;
ALTER TABLE departments_dup
CHANGE COLUMN dept_name dept_name VARCHAR(40) NULL;
# if you don’t currently have ‘departments_dup’ set up
DROP TABLE IF EXISTS departments_dup;
CREATE TABLE departments_dup
(
dept_no CHAR(4) NULL,
dept_name VARCHAR(40) NULL
);
INSERT INTO departments_dup
(
dept_no,
dept_name
)SELECT
*
FROM
departments;
INSERT INTO departments_dup (dept_name)
VALUES ('Public Relations');
DELETE FROM departments_dup
WHERE
dept_no = 'd002';
INSERT INTO departments_dup(dept_no) VALUES ('d010'), ('d011');
Create and fill in the ‘dept_manager_dup’ table, using the following code:
DROP TABLE IF EXISTS dept_manager_dup;
CREATE TABLE dept_manager_dup (
emp_no int(11) NOT NULL,
dept_no char(4) NULL,
from_date date NOT NULL,
to_date date NULL
);
INSERT INTO dept_manager_dup
select * from dept_manager;
INSERT INTO dept_manager_dup (emp_no, from_date)
VALUES (999904, '2017-01-01'),
(999905, '2017-01-01'),
(999906, '2017-01-01'),
(999907, '2017-01-01');
DELETE FROM dept_manager_dup
WHERE
dept_no = 'd001';
INSERT INTO departments_dup (dept_name)
VALUES ('Public Relations');
DELETE FROM departments_dup
WHERE
dept_no = 'd002';
INNER JOIN - Part I
INNER JOIN - Part II
example :
SELECT
m.dept_no, m.emp_no, d.dept_name
FROM
dept_manager_dup m
INNER JOIN
departments_dup d ON m.dept_no = d.dept_no
ORDER BY m.dept_no;
Extract a list containing information about all managers’ employee number, first and last name, department number, and hire date.
SELECT
e.emp_no, e.first_name, e.last_name, d.dept_no, e.hire_date
FROM
employees e
INNER JOIN
dept_manager d ON e.emp_no = d.emp_no
ORDER BY e.emp_no;
A Note on Using Joins
1:) INNER JOIN === JOIN
Duplicate Records
1:) To remove the duplicate from the database : we have to use the GROUP BY column_name . which will remove the duplicates from the retrieved data.
LEFT JOIN - Part I
Join the 'employees' and the 'dept_manager' tables to return a subset of all the employees whose last name is Markovitch. See if the output contains a manager with that name.
Hint: Create an output containing information corresponding to the following fields: ‘emp_no’, ‘first_name’, ‘last_name’, ‘dept_no’, ‘from_date’. Order by 'dept_no' descending, and then by 'emp_no'.
SELECT
e.emp_no,
e.first_name,
e.last_name,
dm.dept_no,
dm.from_date
FROM
employees e
LEFT JOIN
dept_manager dm ON e.emp_no = dm.emp_no
WHERE
e.last_name = 'Markovitch'
ORDER BY dm.dept_no DESC, e.emp_no;
RIGHT JOIN
The new and the old join syntax
Extract a list containing information about all managers’ employee number, first and last name, department number, and hire date. Use the old type of join syntax to obtain the result.
SELECT
e.emp_no,
e.first_name,
e.last_name,
dm.dept_no,
e.hire_date
FROM
employees e,
dept_manager dm
WHERE
e.emp_no = dm.emp_no;
New Join Syntax:
SELECT
e.emp_no,
e.first_name,
e.last_name,
dm.dept_no,
e.hire_date
FROM
employees e
JOIN
dept_manager dm ON e.emp_no = dm.emp_no;
JOIN and WHERE Used Together
Example :
SELECT
e.emp_no, e.first_name, e.last_name, s.salary
FROM
employees e
JOIN
salaries s ON e.emp_no = s.emp_no
WHERE
s.salary > 145000
ORDER BY emp_no;
SELECT
e.first_name, e.last_name, e.hire_date, t.title
FROM
employees e
JOIN
titles t ON e.emp_no = t.emp_no
WHERE
first_name = 'Margareta'
AND last_name = 'Markovitch'
ORDER BY e.emp_no;
Important – Prevent Error Code: 1055!
Depending on your operating system and version of MySQL, you will be working with different SQL settings.
To make sure you can take some of the remaining lectures of the course without unnecessary interruption, we strongly advise you to execute the following query now.
set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '');
If you’re interested in the reason we are asking you to do this, you can read the technical description below. Please be aware that it may sound a bit too abstract at this stage. However, you can always refer back to it once you have completed the lectures about the different types of SQL variables found in the Advanced SQL Tools section of the course.
Technical description:
The GROUP BY clause will be used in several queries by the end of the course. Different versions of the SQL language and MySQL, in particular, can set different limits on how the GROUP BY clause can be applied.
In some of the lectures in the JOINs section, we have opted for queries where multiple table columns that are not included in the GROUP BY clause will be listed in the SELECT statement. We have decided this is the correct approach to teaching the use of the GROUP BY clause at this stage. At the same time, we are aware some versions of MySQL may not allow such queries, or at least not by default.
To adjust the relevant default settings, there is a system variable, called ‘sql_mode’, which needs to be reconfigured.
In order to view the current value of this variable in your case, you have to execute the following command.
select @@global.sql_mode;
An expression containing a few values, separated by commas, will appear in the result grid. They correspond to various MySQL settings that influence the way in which MySQL will behave in different situations.
One of these values, ‘only_full_group_by’, blocks certain type of group statements and that can potentially lead to Error Code 1055. The latter signifies the problem of listing fields in the SELECT statement that are not included in the GROUP BY clause.
Naturally, we want to avoid that. Hence, to prevent it from happening, we must execute the following statement.
set @@global.sql_mode := replace(@@global.sql_mode, 'ONLY_FULL_GROUP_BY', '');
REPLACE() is the function that will remove the “only_full_group_by” value from the expression here. Thus, error 1055 will not show up in the future.
Finally, if for some reason you’d like to disallow this behavior you can always execute the following command which will do exactly the opposite: it will add the “only_full_group_by” value to the expression.
set @@global.sql_mode := concat('ONLY_FULL_GROUP_BY,', @@global.sql_mode);
That said, we must also add that there is a reason behind this functionality. If you think about it, it is not logical to allow a column value to be listed in the output alongside a value that has been included in the GROUP BY clause of the query. We just cannot be sure that the not-grouped value that has been retrieved is going to be correct. But for the sake of our exercises, and for the purpose of making them clearer, we have allowed such syntax.
CROSS - JOIN
Use a CROSS JOIN to return a list with all possible combinations between managers from the dept_manager table and department number 9.
SELECT
dm.*, d.*
FROM
departments d
CROSS JOIN
dept_manager dm
WHERE
d.dept_no = 'd009'
ORDER BY d.dept_name;
Return a list with the first 10 employees with all the departments they can be assigned to.
Hint: Don’t use LIMIT; use a WHERE clause.
SELECT
e.*, d.*
FROM
employees e
CROSS JOIN
departments d
WHERE
e.emp_no < 10011
ORDER BY e.emp_no, d.dept_name;
Using Aggregate Functions with Joins
JOIN more than two tables in SQL
Example :
#joining two or more tables
SELECT
e.first_name,
e.last_name,
e.hire_date,
dm.from_date,
d.dept_name
FROM
employees e
JOIN
dept_manager dm ON e.emp_no = dm.emp_no
JOIN
departments d ON d.dept_no = dm.dept_no;
SELECT
e.first_name,
e.last_name,
e.hire_date,
t.title,
m.from_date,
d.dept_name
FROM
employees e
JOIN
dept_manager m ON e.emp_no = m.emp_no
JOIN
departments d ON m.dept_no = d.dept_no
JOIN
titles t ON e.emp_no = t.emp_no
WHERE t.title = 'Manager'
ORDER BY e.emp_no;
-
- 2nd Solution:
-
SELECT
e.first_name,
e.last_name,
e.hire_date,
t.title,
m.from_date,
d.dept_name
FROM
employees e
JOIN
dept_manager m ON e.emp_no = m.emp_no
JOIN
departments d ON m.dept_no = d.dept_no
JOIN
titles t ON e.emp_no = t.emp_no
AND m.from_date = t.from_date
ORDER BY e.emp_no;
How many male and how many female managers do we have in the ‘employees’ database?
SELECT
e.gender, COUNT(dm.emp_no)
FROM
employees e
JOIN
dept_manager dm ON e.emp_no = dm.emp_no
GROUP BY gender;
ANOTHER SOLUTION :
SELECT
e.gender, t.title, COUNT(e.gender) AS total_count
FROM
employees e
JOIN
titles t ON e.emp_no = t.emp_no
WHERE
t.title = 'Manager'
GROUP BY e.gender;
UNION vs UNION ALL
Go forward to the solution and execute the query. What do you think is the meaning of the minus sign before subset A in the last row (ORDER BY -a.emp_no DESC)?
SELECT
*
FROM
(SELECT
e.emp_no,
e.first_name,
e.last_name,
NULL AS dept_no,
NULL AS from_date
FROM
employees e
WHERE
last_name = 'Denis' UNION SELECT
NULL AS emp_no,
NULL AS first_name,
NULL AS last_name,
dm.dept_no,
dm.from_date
FROM
dept_manager dm) as a
ORDER BY -a.emp_no DESC;
Comments
Post a Comment